mysql - 所有四个部门的通缉犯

Question

问题是，当我运行代码时，它会检查是否有人在部门 1 中，然后停止，但只需要所有四个部门中的人

SELECT
    p.person_id AS ID,
    CONCAT(p.firstname, " ", p.surname) AS 'Employee Name'
FROM
    person AS p, 
    allocation_to_department AS ad
WHERE
    ad.person_id = p.person_id
    AND ad.department_id= ('1,2,3,4')

Answer 1

这是一个聚合查询，将返回属于所有 4 个部门(id 1 到 4)的人员:

SELECT
    p.person_id AS ID,
    CONCAT(p.firstname, " ", p.surname) AS 'Employee Name'
FROM
     person AS p
     INNER JOIN allocation_to_department AS ad
         ON ad.person_id = p.person_id          
GROUP BY 
    p.person_id, 
    p.first_name, 
    p.surname
HAVING 
    MAX(ad.department_id = 1) = 1
    MAX(ad.department_id = 2) = 1
    MAX(ad.department_id = 3) = 1
    MAX(ad.department_id = 4) = 1

另一种解决方案是使用 IN 子句和 COUNT(DISTINCT ...):

SELECT
    p.person_id AS ID,
    CONCAT(p.firstname, " ", p.surname) AS 'Employee Name'
FROM
     person AS p
     INNER JOIN allocation_to_department AS ad
         ON ad.person_id = p.person_id
         AND ad.department_id IN (1, 2, 3, 4)
GROUP BY 
    p.person_id, 
    p.first_name, 
    p.surname
HAVING
    COUNT(DISTINCT ad.department_id) = 4

注意:始终使用显式 JOIN 而不是老派的隐式 连接。我相应地修改了查询。