编写SQL查询：筛选无活跃状态仅含取消/撤销状态的Foreign_ID=301的行

首先，先明确我们的数据源和需求：

原始数据 #

ID  Foreign_ID  Number  Status_Id
1   101         xyz11   3
2   101         xyz22   3
3   201         xyz33   3
4   201         xyz44   1
5   201         xyz55   7
6   301         xyz66   1
7   301         xyz77   7
8   301         xyz88   7

Status_Id 含义 #

• 3: 活跃（active）
• 1: 取消（cancelled）
• 7: 撤销（revoked）

需求拆解 #

我们需要筛选出满足以下所有条件的行：

• 对应的Foreign_ID不存在任何Status_Id=3的记录
• 该Foreign_ID下所有行的Status_Id仅为1或7
• 最终只返回Foreign_ID=301的行

实现SQL查询 #

这里提供两种直观的实现方式：

方法1：使用NOT EXISTS子查询

这种方式逻辑清晰，逐条件排除不符合要求的Foreign_ID：

SELECT *
FROM your_table t_main
WHERE 
  -- 排除存在活跃状态（Status_Id=3）的Foreign_ID
  NOT EXISTS (
    SELECT 1
    FROM your_table t_check_active
    WHERE t_check_active.Foreign_ID = t_main.Foreign_ID
      AND t_check_active.Status_Id = 3
  )
  -- 排除存在非1/7状态的Foreign_ID
  AND NOT EXISTS (
    SELECT 1
    FROM your_table t_check_status
    WHERE t_check_status.Foreign_ID = t_main.Foreign_ID
      AND t_check_status.Status_Id NOT IN (1, 7)
  )
  -- 指定只返回Foreign_ID=301的行
  AND t_main.Foreign_ID = 301;

方法2：使用GROUP BY + HAVING子句

先分组统计每个Foreign_ID的状态特征，再筛选符合条件的分组，最后关联原表获取数据：

SELECT t_main.*
FROM your_table t_main
JOIN (
  SELECT Foreign_ID
  FROM your_table
  GROUP BY Foreign_ID
  HAVING 
    -- 没有任何活跃状态记录
    MAX(CASE WHEN Status_Id = 3 THEN 1 ELSE 0 END) = 0
    -- 所有状态都是1或7
    AND MIN(CASE WHEN Status_Id IN (1,7) THEN 1 ELSE 0 END) = 1
) t_valid ON t_main.Foreign_ID = t_valid.Foreign_ID
WHERE t_main.Foreign_ID = 301;

结果验证 #

执行上述任意查询后，会返回以下符合要求的行：

ID  Foreign_ID  Number  Status_Id
6   301         xyz66   1
7   301         xyz77   7
8   301         xyz88   7

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