← К задачам SQL
← Предыдущая Следующая →

318. Общий руководитель не глава

Expert 1 решил рекурсия

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

Анна Ким и Олег Сафин подходят: ближайший общий — Павел Чернов. Анна Ким и Кирилл Орлов — нет: общий только глава компании. Олег Сафин и Мария Лосева — нет: Олег начальник Марии.

Каждую пару выведите один раз: имя, которое раньше по алфавиту, в employee1.

Используйте таблицу employees. Понадобится WITH RECURSIVE.

Результат: employee1, employee2, manager. Сортировка по employee1, затем по employee2.

Схема базы команды

Схема БД состоит из пяти отношений:

departments — справочник отделов.

  • id — идентификатор;
  • name — название отдела.

employees — сотрудники. manager_id ссылается на эту же таблицу: дерево подчинения. У руководителя компании manager_id равен NULL.

  • id — идентификатор;
  • name — имя;
  • department_id — отдел;
  • manager_id — прямой руководитель, может быть пустым;
  • hired_on — дата найма;
  • salary — зарплата в рублях, тип numeric(10,2).

projects — проекты. Даты начала и конца включительны.

  • id — идентификатор;
  • name — название;
  • start_date — начало проекта;
  • end_date — конец проекта.

assignments — назначение сотрудника на проект. Даты from_date и to_date включительны. Один человек может быть сразу на нескольких проектах, если интервалы пересекаются. Первичный ключ — тройка (employee_id, project_id, from_date).

  • employee_id — сотрудник;
  • project_id — проект;
  • role — роль на проекте: lead, dev, analyst или support;
  • from_date — начало назначения;
  • to_date — конец назначения.

worklog — фактические часы по дням. Нет строки — в этот день на проект не списывали. hours всегда больше нуля.

  • id — идентификатор;
  • employee_id — сотрудник;
  • work_date — рабочий день;
  • hours — часы за день;
  • project_id — проект, на который списаны часы.
Схема данных: departments, employees, projects, assignments, worklog departments Column Name Data Type Nulls id int name text employees Column Name Data Type Nulls id int name text department_id int manager_id int YES hired_on date salary numeric projects Column Name Data Type Nulls id int name text start_date date end_date date assignments Column Name Data Type Nulls employee_id int project_id int role text from_date date to_date date worklog Column Name Data Type Nulls id int employee_id int work_date date hours int project_id int

Данные таблиц

Первые 5 строк каждой таблицы

departments 4

id name
1 Управление
2 Разработка
3 Аналитика
4 Поддержка

employees 13

id name department_id manager_id hired_on salary
1 Ирина Белова 1 None 2019-01-10 280000
2 Павел Чернов 2 1 2020-03-01 210000
3 Елена Юрченко 3 1 2020-05-20 190000
4 Анна Ким 2 2 2021-06-15 220000
5 Олег Сафин 2 2 2021-09-01 150000

projects 6

id name start_date end_date
1 Альфа 2024-01-01 2024-06-30
2 Бета 2024-03-01 2024-12-31
3 Гамма 2024-07-01 2024-09-30
4 Дельта 2024-02-01 2024-04-30
5 Омега 2024-08-01 2024-11-30

assignments 17

employee_id project_id role from_date to_date
2 1 lead 2024-01-01 2024-06-30
3 3 lead 2024-07-01 2024-09-30
4 1 dev 2024-01-01 2024-06-30
4 2 dev 2024-03-01 2024-12-31
4 6 dev 2024-03-15 2024-04-30

worklog 30

id employee_id work_date hours project_id
1 2 2024-03-04 8 1
2 2 2024-03-05 8 1
3 2 2024-03-06 8 1
4 2 2024-03-09 8 1
5 4 2024-03-04 8 1

Данные таблиц

Первые 5 строк. Полная схема — по кнопке выше.

departments 4

id name
1 Управление
2 Разработка
3 Аналитика
4 Поддержка

employees 13

id name department_id manager_id hired_on salary
1 Ирина Белова 1 None 2019-01-10 280000
2 Павел Чернов 2 1 2020-03-01 210000
3 Елена Юрченко 3 1 2020-05-20 190000
4 Анна Ким 2 2 2021-06-15 220000
5 Олег Сафин 2 2 2021-09-01 150000

projects 6

id name start_date end_date
1 Альфа 2024-01-01 2024-06-30
2 Бета 2024-03-01 2024-12-31
3 Гамма 2024-07-01 2024-09-30
4 Дельта 2024-02-01 2024-04-30
5 Омега 2024-08-01 2024-11-30

assignments 17

employee_id project_id role from_date to_date
2 1 lead 2024-01-01 2024-06-30
3 3 lead 2024-07-01 2024-09-30
4 1 dev 2024-01-01 2024-06-30
4 2 dev 2024-03-01 2024-12-31
4 6 dev 2024-03-15 2024-04-30

worklog 30

id employee_id work_date hours project_id
1 2 2024-03-04 8 1
2 2 2024-03-05 8 1
3 2 2024-03-06 8 1
4 2 2024-03-09 8 1
5 4 2024-03-04 8 1

Комментарии (0)

Зарегистрируйтесь или войдите, чтобы оставить комментарий

Пока нет комментариев. Будьте первым!