Задание - вывести фамилию-имя начальника отдела и название отдела, в котором сотрудники выполнили максимальное количество проектов.
У меня получился такой запрос, но выводит он не совсем то, что нужно.
Сделал его по примеру отсюда (второй пункт).
SELECT department_name, max_amount FROM
(
SELECT dep_name AS department_name, COUNT(rel_prj_id) AS projects_amount
FROM employees
INNER JOIN departments ON emp_dep_id = dep_id
INNER JOIN rel_prj_emp ON rel_emp_id = emp_id
GROUP BY dep_name
) X INNER JOIN
(
SELECT MAX(projects_amount) AS max_amount
FROM
(
SELECT dep_name AS department_name, COUNT(rel_prj_id) AS projects_amount
FROM employees
INNER JOIN departments ON emp_dep_id = dep_id
INNER JOIN rel_prj_emp ON rel_emp_id = emp_id
GROUP BY dep_name
) X
) Y ON projects_amount = max_amount
Вывод, при том, что проекта всего три:
В чем проблема - я думаю, вы поймёте, если посмотрете на схему БД. Каждому отделу должно соответствовать несколько проектов, но отделы и проекты связаны через таблицу сотрудников - и поэтому в моём запросе каждому отделу соответствует больше записей, чем нужно.
Группирую по названию отдела, а получается, что в каждой группе - сотрудники этого отдела. Как связать таблицы иначе или сгруппировать иначе - я не придумал, в ступоре нахожусь. Если зайти с другой стороны, сгруппировать по id проекта - в каждой группе будет количество сотрудников, которое работало над проектом, тоже не то.
Нужно решение задачи именно по этой схеме. Я знаю, что можно упростить, но задание учебное.
Скрипт создания БД: http://pastebin.com/hnHnENpX
Заранее спасибо.
Ответ
SELECT emp_first_name,emp_last_name,department_name, max_amount FROM
(
SELECT dep_name AS department_name,e2.emp_first_name,e2.emp_last_name,
COUNT(distinct rel_prj_id) AS projects_amount
FROM employees e1
INNER JOIN departments ON e1.emp_dep_id = dep_id
INNER JOIN rel_prj_emp ON rel_emp_id = e1.emp_id
INNER JOIN employees e2 ON e2.emp_id=dep_manager_id
GROUP BY dep_name,e2.emp_first_name,e2.emp_last_name
) X INNER JOIN
(
SELECT MAX(projects_amount) AS max_amount
FROM
(
SELECT dep_name AS department_name,
COUNT(distinct rel_prj_id) AS projects_amount
FROM employees
INNER JOIN departments ON emp_dep_id = dep_id
INNER JOIN rel_prj_emp ON rel_emp_id = emp_id
GROUP BY dep_name
) X
) Y ON projects_amount = max_amount
Хотя верхний подзапрос немного странно смотрится, логичнее бы выглядело, если бы отделы были первой таблицей и к ней все клеилось. Хотя в данном случае порядок ни на что не влияет (т.к. в inner join таблицы слева и справа равнозначны).
Комментариев нет:
Отправить комментарий