选择仅属于特定部门的用户 [英] Select users belonging only to particular departments

问题描述

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

I have the following table with two fields namely a and b as shown below:

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和Y 。

Note: Now I want to show the employee who is only and only belongs to the department Z and Y. So according to the condition the only employee A103 should be displayed because of he only belongs to the department Z and Y. But employee A101 should not appear because he belong to Z,X, and Y.

预期结果：

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

If condition is : Z and Y then result should be:

empID
------
A103

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

If condition is : Z and X then result should be:

empID
------
A102

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

If condition is : Z,X and Y then result should be:

empID
------
A101

注意：我只想在 where 子句中这样做（不想使用 group by 和具有子句），因为我将在另一个<$ c中包括该子句$ c>哪里。

Note: I want to do it in the where clause only (don't want to use the group by and having clauses), because I'm going to include this one in the other where also.

推荐答案

这是 无余数的关系划分（RDNR） 问题。参见此 文章 由Dwain Camps提供，可以解决此类问题。

This is a Relational Division with no Remainder (RDNR) problem. See this article by Dwain Camps that provides many solution to this kind of problem.

第一个解决方案

SQL小提琴

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小提琴

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 和收藏：

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
    )

这篇关于选择仅属于特定部门的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！