Условия Mysql с группировкой таблиц "многие ко многим"

#php #mysql #sql #database

#php #mysql #sql #База данных

Вопрос:

Мне было интересно, может ли кто-нибудь придумать более элегантные решения моей проблемы. Мне трудно найти похожие случаи.

У меня есть 5 таблиц. 3 — это сведения о сотрудниках, навыках и навыках. Оставшиеся 2 являются связующими таблицами.

ссылки на навыки

 skill_id    subskill_id
1           4
1           5
2           4
2           6

emp_skill_links

 employee_id    subskill_id    acquired
1              4              2013-04-05 00:00:00
1              5              2014-02-24 00:00:00
2              6              2012-02-26 00:00:00
2              5              2011-06-14 00:00:00

Оба имеют отношения «многие ко многим». Навыки с subskills (skill_links) и сотрудники с subskills (emp_skill_links).

Я хочу выбрать сотрудников, которые приобрели все дополнительные навыки для навыка. Я попытался сделать это с помощью одного запроса, но не смог справиться с этим с помощью группировки. На данный момент мое решение — это два отдельных запроса и последующее сопоставление их в массиве php. Это:

 SELECT sl.skill_id, COUNT(sl.subskill_id) as expected
FROM skill_links sl
GROUP BY sl.skill_id

для сравнения с:

 SELECT sl.skill_id, esl.employee_id, COUNT(esl.subskill_id) as provided
FROM emp_skill_links esl
INNER JOIN skill_links sl
ON sl.subskill_id = esl.subskill_id
GROUP BY sl.skill_id, esl.employee_id

Есть ли более эффективное решение с одним запросом для моей проблемы? Или это не стоило бы такой сложности?

1. employee_id И каким- skill_id то образом напрямую связаны? В противном случае это не имеет смысла, поскольку subskill_id не является уникальным.

2. Нет. В структуре базы данных, которую я имею на данный момент, единственная связь между ними — через skill_links.

3. Вы имеете в виду только через subskill_id поле? Тогда он должен работать, только если subskill_id он уникален.

4. subskill_id — моя единственная ссылка на employee-subskills-skills. Я не уверен, какую уникальность вы имеете в виду. Поднаборы каждого сотрудника уникальны, но у многих сотрудников могут быть совпадающие поднаборы.

Ответ №1:

Если вы рассматриваете запрос, состоящий из подзапросов, как отвечающий вашему требованию «более эффективного решения с одним запросом» (зависит от вашего определения «одного запроса»), то это сработает.

 SELECT employeeTable.employee_id
FROM
  (SELECT sl.skill_id, COUNT(*) AS subskill_count
  FROM skill_links sl
  GROUP BY sl.skill_id) skillTable
  JOIN
  (SELECT esl.employee_id, sl2.skill_id, COUNT(*) AS employee_subskills
   FROM emp_skill_links esl
     JOIN skill_links sl2 ON esl.subskill_id = sl2.subskill_id
   GROUP BY esl.employee_id, sl2.skill_id) employeeTable
  ON skillTable.skill_id = employeeTable.skill_id
 WHERE employeeTable.employee_subskills = skillTable.subskill_count

Что делает запрос:

Выберите количество вспомогательных навыков для каждого навыка
Выберите количество вспомогательных навыков для каждого сотрудника для каждого основного навыка
Объедините эти результаты на основе основного навыка
Выберите сотрудников из числа тех, у кого количество под-навыков равно количеству под-навыков для основного навыка

ДЕМОНСТРАЦИЯ

В примере is пользователи 1 и 3 обладают всеми вспомогательными навыками основного навыка 1. Пользователь 2 имеет только 2 из 3 вспомогательных навыков основного навыка 2.

Вы заметите, что логика здесь похожа на то, что вы уже делаете, но у нее есть преимущество только в одном запросе к БД (вместо двух), и она не включает в себя работу PHP по созданию, перебору, сравнению и сокращению массивов.