Как составить запрос в mysql в котором есть count?

Question

[Konstantin]

Всем привет. Есть две таблицы менеджеры и заказы

Структура таблицы manager

CREATE TABLE `manager` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(32) CHARACTER SET latin1 NOT NULL,
  `last_name` VARCHAR(32) CHARACTER SET latin1 NOT NULL,
  `email` VARCHAR(32) CHARACTER SET latin1 NOT NULL,
  `chief_id` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `chief_id` (`chief_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

Структура таблицы claim

CREATE TABLE `claim` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `manager_id` INT(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `sum` FLOAT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `manager_id` (`manager_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

Ограничения внешнего ключа таблицы claim

ALTER TABLE `claim`
  ADD CONSTRAINT `claim_ibfk_1` FOREIGN KEY (`manager_id`) 
  REFERENCES `manager` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Ограничения внешнего ключа таблицы manager

ALTER TABLE `manager`
  ADD CONSTRAINT `manager_ibfk_1` FOREIGN KEY (`chief_id`) 
  REFERENCES `manager` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

Помогите пожалуйста выбрать менеджеров, у которых количество заказов больше, чем у их руководителя. Не могу никак выбрать. Самый близкий вариант такой

SELECT a.first_name, b.first_name, COUNT( a.first_name ) , COUNT( b.first_name ) 
FROM manager a, manager b,  `claim` 
WHERE a.id = b.chief_id AND a.id = claim.manager_id 
GROUP BY a.first_name

Но куда впихнуть условие где count одного > count другого, ума не приложу. Если в WHERE ставлю
AND COUNT( a.first_name ) > COUNT( b.first_name )

получаю ошибку Invalid use of group function если ставлю в having

SELECT a.first_name, b.first_name, COUNT( a.first_name ), COUNT( b.first_name )
 FROM manager a, manager b, claim WHERE a.id = b.chief_id 
AND a.id = claim.manager_id GROUP BY a.first_name 
HAVING COUNT( a.first_name ) > COUNT( b.first_name )

то ответ пустой получается.

Answer 1

[Петр]

select mngrs.manager_id from
(select mn.chief_id, cl.manager_id, count(1) cn
  from claim cl
inner join manager mn on (mn.id = cl.manager_id) and (mn.chief_id is not null)
group by mn.chief_id, cl.manager_id) mngrs
inner join 
(select cl.manager_id chiefid, count(1) cn
  from claim cl
inner join manager mn on (mn.id = cl.manager_id) and (mn.chief_id is null)
group by cl.manager_id) chiefs on chiefs.chiefid = mngrs.chief_id
where mngrs.cn > chiefs.cn

Запрос не проверял, но идея должна быть примерно понятна

Comments

[Konstantin]

Спасибо огромное

Answer 2

[Rsa97]

Разбейте задачу на подзадачи, затем соберите воедино. Решение может быть неоптимальным, но работать будет
1. Количество заказов по сотрудникам:

SELECT `manager_id`, COUNT(*) AS `count`
    FROM `claim`
    GROUP BY `manager_id`

2. Сотрудники, имеющие руководителей

SELECT `id`, `first_name`, `last_name`, `email` 
    FROM `manager` 
    WHERE `chief_id` IS NOT NULL

3. Объединяем запросы. Подсоединяем таблицу с количеством для менеджера (JOIN, потому что варианты с нулём заказов у менеджера нас не интересуют) и его руководителя (LEFT JOIN, ноль у начальника - приемлемый вариант), выбираем строки где у руководителя нет заказов или их меньше, чем у подчинённого.

SELECT `m`.`id`, `m`.`first_name`, `m`.`last_name`, `m`.`email` 
    FROM `manager` AS `m`
    JOIN (
        SELECT `manager_id`, COUNT(*) AS `count`
        FROM `claim`
        GROUP BY `manager_id`
    ) AS `mc` ON `mc`.`manager_id` = `m`.`id`
    LEFT JOIN (
        SELECT `manager_id`, COUNT(*) AS `count`
        FROM `claim`
        GROUP BY `manager_id`
    ) AS `cc` ON `cc`.`manager_id` = `m`.`chief_id`
    WHERE `chief_id` IS NOT NULL
        AND (`cc`.`count` IS NULL
            OR `mc`.`count` > `cc`.`count`)

Comments

[Konstantin]

Спасибо огромное