sql - 选择仅属于特定部门的用户

我有下表，其中包含两个字段，即a和b，如下所示:

create table employe
(
    empID varchar(10),
    department varchar(10)
);

插入一些记录:

insert into employe values('A101','Z'),('A101','X'),('A101','Y'),('A102','Z'),('A102','X'),
             ('A103','Z'),('A103','Y'),('A104','X'),('A104','Y'),('A105','Z'),('A106','X');


select * from employe;

empID   department
------------------
A101    Z
A101    X
A101    Y
A102    Z
A102    X
A103    Z
A103    Y
A104    X
A104    Y
A105    Z
A106    X

注意:现在，我想向员工展示唯一且仅属于部门Z和Y的员工。
因此，根据条件，仅应显示雇员A103，因为他仅属于
到部门Z和Y。但是员工A101不应出现，因为他属于Z,X, and Y。

预期结果:

如果条件为:Z和Y，则结果应为:

empID
------
A103

如果条件为:Z和X，则结果应为:

empID
------
A102

如果条件为:Z，X和Y，则结果应为:

empID
------
A101

注意:我只想在where子句中执行此操作(不想使用group by和having子句)，因为我还将在另一个where中包括此子句。

最佳答案

这是一个关系部门，没有剩余(RDNR)问题。请参阅Dwain Camps的article，它为此类问题提供了许多解决方案。

第一个解决方案

SQL Fiddle

SELECT empId
FROM (
    SELECT
        empID, cc = COUNT(DISTINCT department)
    FROM employe
    WHERE department IN('Y', 'Z')
    GROUP BY empID
)t
WHERE
    t.cc = 2
    AND t.cc = (
        SELECT COUNT(*)
        FROM employe
        WHERE empID = t.empID
    )

第二解决方案

SQL Fiddle

SELECT e.empId
FROM employe e
WHERE e.department IN('Y', 'Z')
GROUP BY e.empID
HAVING
    COUNT(e.department) = 2
    AND COUNT(e.department) = (SELECT COUNT(*) FROM employe WHERE empID = e.empId)

不使用GROUP BY和HAVING:

SELECT DISTINCT e.empID
FROM employe e
WHERE
    EXISTS(
        SELECT 1 FROM employe WHERE department = 'Z' AND empID = e.empID
    )
    AND EXISTS(
        SELECT 1 FROM employe WHERE department = 'Y' AND empID = e.empID
    )
    AND NOT EXISTS(
        SELECT 1 FROM employe WHERE department NOT IN('Y', 'Z') AND empID = e.empID
    )

关于sql - 选择仅属于特定部门的用户，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30092232/