如何编写SQL查询：排除同一Subject下已关联Valid状态的Expired记录

需求说明 #

编写SELECT查询需满足以下规则：

• 当同一empid+Subject组合下存在Valid状态记录时，排除该组合下的Expired状态结果
• 若仅查询Expired状态，需返回0行结果

测试数据 #

现有查询及结果 #

查询1：无状态过滤分组 #

SELECT empid, subject 
FROM t1 
GROUP BY empid, subject

结果：

返回2行数据。

查询2：仅过滤Expired状态分组 #

SELECT empid, subject 
FROM t1 
WHERE (status = 'Expired')
GROUP BY empid, subject

结果：

返回1行数据。

查询3：仅过滤Valid状态分组 #

SELECT empid, subject FROM t1 WHERE (status = 'Valid')
GROUP BY empid, subject

结果：

返回2行数据。

符合需求的解决方案 #

场景1：获取所有合规记录（排除有Valid的组合下的Expired） #

该查询保留所有Valid记录，仅保留无对应Valid记录的Expired记录（若存在）：

SELECT empid, subject, status
FROM t1
WHERE 
  status = 'Valid'
  OR NOT EXISTS (
    SELECT 1
    FROM t1 AS t2
    WHERE t2.empid = t1.empid
      AND t2.subject = t1.subject
      AND t2.status = 'Valid'
  );

结果：

场景2：查询Expired状态时返回0行 #

该查询过滤掉所有存在对应Valid记录的Expired条目，测试数据中返回0行：

SELECT empid, subject, status
FROM t1
WHERE 
  status = 'Expired'
  AND NOT EXISTS (
    SELECT 1
    FROM t1 AS t2
    WHERE t2.empid = t1.empid
      AND t2.subject = t1.subject
      AND t2.status = 'Valid'
  );

通用分组查询（仅返回empid+subject组合） #

如果只需返回分组后的empid和subject，可使用以下查询：

SELECT empid, subject
FROM t1
GROUP BY empid, subject
HAVING SUM(CASE WHEN status = 'Valid' THEN 1 ELSE 0 END) > 0
OR (SUM(CASE WHEN status = 'Expired' THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN status = 'Valid' THEN 1 ELSE 0 END) = 0);

测试数据中返回结果：

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