SQL:多对多表AND查询 [英] SQL: Many-To-Many table AND query

问题描述

首先-对模糊标题的道歉，我找不到更好的标题.

First - apologies for the fuzzy title, I could not find a better one.

我的表格具有以下结构(简化):

I have table with the following structure (simplification):

EmpID DeptID

1     1
1     2
2     1
3     2
4     5
5     2

此表表示多对多关系.

我有兴趣查找与特定DeptID组相关的所有EmpID，例如，我想要与DeptID 1、2和3相关的所有EmpID.请注意，这是AND关系，而不是OR关系.对于我来说，EmpID可能与除1、2和3之外的其他DeptID有关，以使其成为有效答案.

I'm interested in finding all the EmpIDs that are related to a specific group of DeptIDs, for example I want all the EmpIDs that are related to DeptIDs 1, 2 and 3. Please note it's an AND relationship and not an OR relationship. For my case, the EmpID may be related to additional DeptIDs besides 1, 2 and 3 for it to be a valid answer.

我对更改感兴趣的DeptID的数量(即，我可能想要与DeptID 3和5相关的EmpID，或者我可能想要与DepID 2、3、4、5、6、7相关的EmpID)

The number of DeptIDs I'm interested in changes (i.e. I may want EmpIDs who're related to both DeptID 3 and 5, or I may want EmpIDs related to DepIDs 2, 3, 4, 5, 6, 7).

当我尝试解决此问题时，我发现自己要么根据每个DepID创建一个JOIN，要么针对每个DeptID创建一个子查询.这意味着我必须针对要测试的DeptID数量生成一个新查询.我显然希望使用带有一个参数或一组参数的静态查询.

When I try to approach this problem I find myself either creating a JOIN per DepID, or a subquery per DeptID. This would mean I have to generate a new query per the number of DeptIDs I'm testing against. I would obviously prefer having a static query with a parameter or set of parameters.

我正在同时使用SQL Server和MySQL(同时开发两个版本的代码).

I'm working over both SQL Server and MySQL (developing in parallel two versions of my code).

有什么想法吗?

推荐答案

我假设您要查找指定部门 ALL 中的雇员，而不仅仅是查找<每个部门的strong> ANY ，这是一个简单得多的查询.

I'm assuming you want to find employees that are in ALL of the specified departments and not just the employees that are in ANY of the departments, which is a far easier query.

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

我将避免使用聚合的不可避免的建议:

I'm going to preempt the inevitable suggestion that'll come to use aggregation:

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

抵制这种诱惑.它的速度显着. SQL语句-中出现了与此类似的情况加入"与分组依据并拥有" 相比，第二个版本在第二个版本中慢了大约[strong] 20倍.

Resist that temptation. It's significantly slower. A similar scenario to this came up in SQL Statement - "Join" Vs "Group By and Having" and the second version was, in that second, about twenty times slower.

我还建议您查看 AppDevelopers犯的数据库开发错误.

这篇关于SQL:多对多表AND查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！