我有一张下表,我想选择应该具有所有提及值的记录。例如
选择CompanyID
,它应具有所有三个ProviderID
200,300,400。因此,运行查询应重新运行CompanyID
2。与IN
相反。提前致谢 !
CompanyID | ProviderID
--------------------------------------
1 100
2 200
3 500
4 600
2 300
2 400
7 100
最佳答案
执行GROUP BY
,仅选择具有3个所需EmployeeID值中的任何一个的行。使用HAVING
确保获得3个不同的EmployeeID值。
select CompanyID
from tablename
where EmployeeID IN (200, 300, 400)
group by CompanyID
having count(distinct EmployeeID) = 3
另一种方法是双重自连接,每个EmployeeID一个。但是,这不太灵活:
select distinct t1.CompanyID
from (select CompanyID from tablename where EmployeeID = 200) t1
join (select CompanyID from tablename where EmployeeID = 300) t2
on t1.CompanyID = t2.CompanyID
join (select CompanyID from tablename where EmployeeID = 400) t3
on t2.CompanyID = t3.CompanyID
关于mysql - MySQL:从多对多表中获取条目,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/35264921/