Страницы

Поиск по вопросам

среда, 10 июля 2019 г.

Помогите пожалуйста с запросом SQL (Oracle). Having, Group by, агрегатная функция от агрегатной функции

Задание - вывести фамилию-имя начальника отдела и название отдела, в котором сотрудники выполнили максимальное количество проектов. У меня получился такой запрос, но выводит он не совсем то, что нужно. Сделал его по примеру отсюда (второй пункт).

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 таблицы слева и справа равнозначны).

Комментариев нет:

Отправить комментарий