count & having example

count-groupBy-having.sql

DROP TABLE IF EXISTS `user`;
DROP TABLE IF EXISTS `video`;

CREATE TABLE IF NOT EXISTS `user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
  `access` TINYINT UNSIGNED,
  PRIMARY KEY `pk_id`(`id`)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `video` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
  PRIMARY KEY `pk_id`(`id`)
) ENGINE = InnoDB;

INSERT INTO `user`(`name`, `access`) VALUES
('Berry Pettrey', 4),
('Scarlet Collicott', 2),
('Bulah Kamens', 1),
('Tressie Bagnaschi', 5)
;

INSERT INTO `video`(`name`) VALUES
('Long Creedon'),
('Kenneth Schoninger'),
('Valeri Jorski')
;

SELECT
    `u`.*, COUNT(*) AS `video_count`
  FROM `user` AS `u`, `video` AS `v`
  GROUP BY `u`.`id`
  HAVING ( `u`.`access` < `video_count` ) 

Вот еще способ. Этот лучше. Будет быстрее работать и проще для понимания.

SELECT
    `u`.*
  FROM `user` AS `u`, (SELECT COUNT(*) AS `count` FROM `video`) AS `v`
  WHERE ( `u`.`access` < `v`.`count` )

Обновить записи по тому же условию можно так

UPDATE
    `user` AS `u`,
    (SELECT COUNT(*) AS `count` FROM `video`) AS `v`
SET
  `u`.`access` = `u`.`access`+1
WHERE ( `u`.`access` < `v`.`count` );