SELECT Users.idUser
FROM Users
INNER JOIN Licenses ON Licenses.IdUser = Users.idUser
INNER JOIN LicensesEquipments ON LicensesEquipments.IdLicense = Licenses.IdLicense
INNER JOIN Equipment ON Equipment.IdEquipment = LicensesEquipments.IdEquipment
WHERE Equipment.IdEquipment IN ('A', 'B', 'C')
GROUP BY IdUser
HAVING COUNT(DISTINCT Equipment.IdEquipment) = 3 <--must be # of unique items in IN clause
如果您确实只需要用户ID,则可以从查询中删除Users
和Licenses
表:
SELECT Licenses.IdUser
FROM Licenses
INNER JOIN LicensesEquipments ON LicensesEquipments.IdLicense = Licenses.IdLicense
WHERE LicensesEquipments.IdEquipment in ('A', 'B', 'C')
GROUP BY Licenses.IdUser
HAVING COUNT(DISTINCT LicensesEquipments.IdEquipment) = 3
另外,一些别名使阅读起来更容易:
SELECT l.IdUser
FROM Licenses l
INNER JOIN LicensesEquipments le ON le.IdLicense = l.IdLicense
WHERE le.IdEquipment in ('A', 'B', 'C')
GROUP BY l.IdUser
HAVING COUNT(DISTINCT le.IdEquipment) = 3