SQL: много-много-таблица таблицы AND

Сначала - извинения за нечеткое название, я не смог найти лучшего.

У меня есть таблица со следующей структурой (упрощение):

В этой таблице представлено отношение "многие ко многим".

Мне интересно найти все EmpID, относящиеся к определенной группе DeptID, например, я хочу, чтобы все EmpIDs были связаны с DeptIDs 1, 2 и 3. Обратите внимание, что это отношение AND, а не OR отношения. Для моего случая, EmpID может быть связан с дополнительными DeptIDs, кроме 1, 2 и 3, чтобы он был действительным ответом.

Число DeptIDs, в которых меня интересуют изменения (например, я могу захотеть EmpIDs, которые связаны с DeptID 3 и 5, или я могу захотеть EmpIDs, связанных с DepIDs 2, 3, 4, 5, 6, 7).

Когда я пытаюсь подойти к этой проблеме, я нахожу себя либо создающим JOIN для DepID, либо подзапросом для DeptID. Это означало бы, что я должен сгенерировать новый запрос на количество DeptID, с которыми я тестирую. Я бы предпочел иметь статический запрос с параметром или набором параметров.

Я работаю над SQL Server и MySQL (параллельно разрабатывая две версии моего кода).

Ответы

Ответ 1

Я предполагаю, что вы хотите найти сотрудников, которые находятся в ВСЕ указанных отделов, а не только сотрудники, которые находятся в ЛЮБОЙ отделах, что является гораздо проще.

SELECT EmpID
FROM mytable t1
JOIN mytable t2 ON t1.EmpID = t2.EmpID AND t2.DeptID = 2
JOIN mytable t3 ON t2.EmpID = t3.EmpID AND t3.DeptID = 3
WHERE DeptID = 1

Я собираюсь упредить неизбежное предложение, которое приступит к использованию агрегации:

SELECT EmpID
FROM mytable
WHERE DeptID IN (1,2,3)
GROUP BY EmpID
HAVING COUNT(1) = 3

Сопротивляй этому искушению. Он значительно медленнее. Аналогичный сценарий для этого появился в SQL Statement - "Join" Vs "Group By and Have" , а вторая версия была в этой секунде примерно двадцать раза медленнее.

Я также предлагаю вам взглянуть на Ошибки разработки баз данных, сделанные AppDevelopers.

Ответ 2

Я бы начал с чего-то вроде:

SELECT EmpID, COUNT(*) AS NumDepts
FROM thetable
WHERE DeptID IN (1, 2, 3)
GROUP BY EmpId
HAVING COUNT(*) == 3

конечно, что 3 в последней строке всегда будет длиной последовательности идентификаторов отделов, которые вы проверяете (так что для (2,3,4,5,6,7) это будет 6). Это один из естественных способов выражения "служащих, связанных со всеми этими отделами".

Изменить: я вижу примечание в другом ответе о проблемах с производительностью. Я пробовал этот подход в SQLite и PostgreSQL с соответствующими индексами, и там он выглядит так, как будто он хорошо работает и с соответствующим использованием всех указанных индексов; и в MySQL 5.0, где я должен признать, что производительность нигде не была хорошей.

Я подозреваю (без возможности сравнить это на еще два миллиона двигателей;-), что другие действительно хорошие SQL-движки (такие как SQL Server 2008, Oracle, IBM DB2, новый с открытым исходным кодом Ingres...) также будут оптимизируйте этот запрос, в то время как другие посредственные (не могут думать ни о какой популярности в любом месте рядом с MySQL) не будут.

Таким образом, несомненно, ваш любимый ответ будет зависеть от того, какие двигатели вы действительно заботитесь (это возвращает меня к тому времени, более десяти лет назад, когда мои обязанности включали управление командой, которая поддерживала компонент, который должен был обеспечить - выполнение запросов более чем на полдюжины разрозненных движков - расскажите о работе кошмара...! -).