如何编写Oracle查询筛选仅含Scientist职位的部门ID

问题背景 #

现有Oracle的Department表，结构如下：

Department Table
department_id varchar(25) primary key,
employee_id  number,
occupation  varchar(25)
salary      number
as_of_date  date

数据示例：

需求：筛选出仅拥有Scientist职位、无其他职位类型的部门ID，同时需限定as_of_date在2024-11-11至2024-11-26之间。

原查询的问题 #

原查询先通过WHERE occupation = 'Scientist'过滤了数据，导致分组时只能看到该部门的Scientist职位，无法判断是否存在其他职位，因此会错误将d001这类包含多种职位的部门也纳入结果。

正确查询语句 #

方法一：排除存在非Scientist职位的部门 #

SELECT DISTINCT department_id
FROM Department
WHERE as_of_date BETWEEN DATE '2024-11-11' AND DATE '2024-11-26'
AND department_id NOT IN (
    SELECT department_id
    FROM Department
    WHERE occupation != 'Scientist'
    AND as_of_date BETWEEN DATE '2024-11-11' AND DATE '2024-11-26'
);

方法二：分组统计验证所有职位均为Scientist #

SELECT department_id
FROM Department
WHERE as_of_date BETWEEN DATE '2024-11-11' AND DATE '2024-11-26'
GROUP BY department_id
HAVING SUM(CASE WHEN occupation != 'Scientist' THEN 1 ELSE 0 END) = 0;

另一种等价写法：

SELECT department_id
FROM Department
WHERE as_of_date BETWEEN DATE '2024-11-11' AND DATE '2024-11-26'
GROUP BY department_id
HAVING COUNT(DISTINCT occupation) = 1
AND MAX(occupation) = 'Scientist';

方法三：窗口函数标记不符合条件的部门 #

SELECT DISTINCT department_id
FROM (
    SELECT department_id,
           MAX(CASE WHEN occupation != 'Scientist' THEN 1 ELSE 0 END) OVER (PARTITION BY department_id) AS has_non_scientist
    FROM Department
    WHERE as_of_date BETWEEN DATE '2024-11-11' AND DATE '2024-11-26'
) t
WHERE has_non_scientist = 0;

关键说明 #

• Oracle中建议用DATE 'YYYY-MM-DD'显式声明日期，避免字符串转日期的隐式转换问题。
• 所有方法均保留对as_of_date的过滤，确保仅统计指定时间段内的职位数据。

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