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

Question

我有下表，其中包含两个字段，即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 byandhaving子句），因为我where还将在另一个子句中包含该子句。

Answer 1

第一个解决方案

sqlfiddle

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
    )

第二解决方案

sqlfiddle

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
    )