求SQL查询语句：筛选同时有男女员工的部门数据

表结构与测试数据 #

CREATE TABLE departments(deptname VARCHAR2(20),sex VARCHAR2(2));

INSERT INTO departments VALUES('SALES','M');
INSERT INTO departments VALUES('SALES','F');
INSERT INTO departments VALUES('SALES','M');
INSERT INTO departments VALUES('SALES','F');
INSERT INTO departments VALUES('ENG','M');
INSERT INTO departments VALUES('ENG','F');
INSERT INTO departments VALUES('MKT','M');
INSERT INTO departments VALUES('CLE','F');
INSERT INTO departments VALUES('AUTO','M');
INSERT INTO departments VALUES('AUTO','M');
INSERT INTO departments VALUES('ENV','F');
INSERT INTO departments VALUES('ENV','F');

需求说明 #

查询所有同时拥有男性（'M'）和女性（'F'）员工的部门，并返回这些部门的全部员工记录。

方法一：子查询筛选目标部门 #

先找出符合条件的部门，再关联原表获取对应员工记录：

SELECT d.deptname, d.sex
FROM departments d
WHERE d.deptname IN (
    SELECT deptname
    FROM departments
    GROUP BY deptname
    HAVING COUNT(DISTINCT sex) = 2
);

思路 #

1. 子查询通过GROUP BY deptname按部门分组，用COUNT(DISTINCT sex) = 2筛选出同时存在两种性别的部门；
2. 外层查询仅保留这些部门的所有员工数据。

方法二：窗口函数实现（Oracle 11g+适用） #

通过窗口函数计算每个部门的性别种类数，再过滤出符合条件的记录：

SELECT deptname, sex
FROM (
    SELECT 
        deptname, 
        sex,
        COUNT(DISTINCT sex) OVER (PARTITION BY deptname) AS sex_count
    FROM departments
) t
WHERE t.sex_count = 2;

思路 #

1. 内层查询用PARTITION BY deptname按部门分区，统计每个部门的不同性别数量；
2. 外层查询筛选出性别数量为2的部门的全部记录。

方法三：自连接筛选 #

通过自连接匹配同部门不同性别的记录，间接获取目标部门的员工数据：

SELECT DISTINCT d1.deptname, d1.sex
FROM departments d1
JOIN departments d2 
    ON d1.deptname = d2.deptname 
    AND d1.sex != d2.sex;

思路 #

1. 表自连接后，关联条件限定为部门相同、性别不同，确保匹配到的部门同时存在男女员工；
2. 用DISTINCT去除自连接产生的重复记录，得到目标结果。

测试结果 #

以上三种方法执行后，均会输出预期结果：

Deptname   Sex
SALES      M
SALES      F
SALES      M
SALES      F
ENG        M
ENG        F

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