SQLite技术问询：如何查询同时属于两个部门列表的员工ID

你的原查询没有结果，核心原因是单条DepartmentLink记录的departmentId不可能同时属于两个不重叠的部门列表（比如既在1/2/3里，又等于100），这是逻辑矛盾，自然查不到数据。

在SQLite里完全可以实现你的需求，下面提供几种实用方案：

方案1：分组统计+HAVING筛选 #

通过分组员工ID，统计其满足两个部门条件的记录数，确保两个条件都至少满足一次：

SELECT dl.employeeId
FROM DepartmentLink dl
WHERE dl.departmentId IN (1,2,3) OR dl.departmentId = 100
GROUP BY dl.employeeId
HAVING 
    SUM(CASE WHEN dl.departmentId IN (1,2,3) THEN 1 ELSE 0 END) >= 1
    AND SUM(CASE WHEN dl.departmentId = 100 THEN 1 ELSE 0 END) >= 1;

如果需要关联员工表获取姓名，直接添加JOIN即可：

SELECT e.id, e.name
FROM Employee e
INNER JOIN DepartmentLink dl ON e.id = dl.employeeId
WHERE dl.departmentId IN (1,2,3) OR dl.departmentId = 100
GROUP BY e.id, e.name
HAVING 
    SUM(CASE WHEN dl.departmentId IN (1,2,3) THEN 1 ELSE 0 END) >= 1
    AND SUM(CASE WHEN dl.departmentId = 100 THEN 1 ELSE 0 END) >= 1;

方案2：EXISTS子查询 #

通过两个EXISTS分别验证员工是否存在于两个部门的关联记录中，逻辑更直观：

SELECT e.id, e.name
FROM Employee e
WHERE 
    EXISTS (
        SELECT 1 FROM DepartmentLink dl 
        WHERE dl.employeeId = e.id AND dl.departmentId IN (1,2,3)
    )
    AND EXISTS (
        SELECT 1 FROM DepartmentLink dl 
        WHERE dl.employeeId = e.id AND dl.departmentId = 100
    );

方案3：两次关联DepartmentLink #

通过两次内连接，筛选同时存在两个部门关联的员工：

SELECT DISTINCT e.id, e.name
FROM Employee e
INNER JOIN DepartmentLink dl1 ON e.id = dl1.employeeId AND dl1.departmentId IN (1,2,3)
INNER JOIN DepartmentLink dl2 ON e.id = dl2.employeeId AND dl2.departmentId = 100;

这里加DISTINCT是避免同一员工因关联多个A列表部门而重复出现。

内容的提问来源于stack exchange，提问作者Christiaan Maks