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

我有下表，其中包含两个字段，即 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

Note：现在我想显示唯一且仅属于该部门的员工Z and Y。 所以根据条件唯一的员工A103应该显示，因为他只属于 到部门Z and Y。但员工A101不应该出现，因为他属于Z,X, and Y.

预期结果:

如果条件是：Z and Y那么结果应该是：

empID
------
A103

如果条件是：Z and X那么结果应该是：

empID
------
A102

如果条件是：Z,X and Y那么结果应该是：

empID
------
A101

Note: 我想在where仅子句（不想使用group by and having条款），因为我要把这个包含在另一个中where also.

这是一个无余数关系除法 (RDNR)问题。看到这个article https://www.simple-talk.com/sql/learn-sql-server/high-performance-relational-division-in-sql-server/Dwain Camps 的论文为此类问题提供了许多解决方案。

第一个解决方案

SQL小提琴 http://sqlfiddle.com/#!6/6d9cf/2/0

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小提琴 http://sqlfiddle.com/#!6/6d9cf/3/0

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 and 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
    )