Oracle SQL查询：筛选同时拥有New和Old状态的员工

需求回顾 #

从包含ENO（员工编号）、Sequence（序列）、Status（状态）字段的employee表中，筛选出同时存在'New'和'Old'两种状态的员工，并且排除员工编号为C456的记录。

解决方案1：分组统计法 #

通过分组后统计状态类型，确保员工仅包含'New'和'Old'两种状态且同时存在：

SELECT ENO
FROM employee
WHERE ENO != 'C456'
GROUP BY ENO
HAVING -- 确保同时拥有New和Old状态
       SUM(CASE WHEN Status = 'New' THEN 1 ELSE 0 END) >= 1
   AND SUM(CASE WHEN Status = 'Old' THEN 1 ELSE 0 END) >= 1
   -- 可选：排除存在其他状态的员工，若允许员工有其他状态可删除此条件
   AND SUM(CASE WHEN Status NOT IN ('New', 'Old') THEN 1 ELSE 0 END) = 0;

逻辑说明：

• WHERE ENO != 'C456'：直接过滤掉指定员工编号
• GROUP BY ENO：按员工编号分组聚合
• HAVING子句：分别统计该员工'New'、'Old'状态的记录数，确保至少各有1条；额外判断是否存在其他状态，避免不符合要求的员工被选中

解决方案2：EXISTS子查询法 #

通过子查询验证员工状态的存在性，逻辑更直观：

SELECT DISTINCT e.ENO
FROM employee e
WHERE e.ENO != 'C456'
  -- 验证该员工存在New状态记录
  AND EXISTS (SELECT 1 
              FROM employee e1 
              WHERE e1.ENO = e.ENO 
                AND e1.Status = 'New')
  -- 验证该员工存在Old状态记录
  AND EXISTS (SELECT 1 
              FROM employee e2 
              WHERE e2.ENO = e.ENO 
                AND e2.Status = 'Old')
  -- 可选：排除存在其他状态的员工，若允许员工有其他状态可删除此条件
  AND NOT EXISTS (SELECT 1 
                  FROM employee e3 
                  WHERE e3.ENO = e.ENO 
                    AND e3.Status NOT IN ('New', 'Old'));

逻辑说明：

• DISTINCT：避免同一员工因多条状态记录重复输出
• 三个EXISTS子查询分别验证：员工存在'New'状态、存在'Old'状态、不存在其他状态
• 若ENO和Status字段建有索引，该方案在大数据量场景下性能更优

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