Вопрос по sql – SQL: таблица «многие ко многим» и запрос

13

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

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

<code>EmpID DeptID

1     1
1     2
2     1
3     2
4     5
5     2
</code>

Эта таблица представляет отношение многих ко многим.

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

Количество DeptID, которые меня интересуют (например, мне могут понадобиться EmpID, которые связаны с DeptID 3 и 5, или мне могут понадобиться EmpID, связанные с DepID 2, 3, 4, 5, 6, 7).

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

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

Любые идеи

Ваш Ответ

2   ответа
14

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

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 - «Присоединяйся» против «Группируй и имея» и вторая версия была, в ту секунду, о в двадцать раз медленнее.

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

3

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

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) не будут.

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

Похожие вопросы