如何在MySQL中编写关联查询以按状态条件过滤记录

核心逻辑说明 #

我们通过两次左连接或者分组过滤的方式实现需求，核心是排除所有关联过S状态t2记录的t1数据，同时保留无关联t2记录的t1数据。 #

方案1：分组过滤写法（推荐，可直接返回对应最新状态） #

SELECT 
  t1.id AS t1_id,
  MAX(t2.status) AS status
FROM t1
LEFT JOIN t2 ON t1.id = t2.t1_id
GROUP BY t1.id
-- 过滤掉存在S状态的t1分组
HAVING COUNT(CASE WHEN t2.status = 'S' THEN 1 END) = 0;

逻辑解释 #

• LEFT JOIN保证没有关联t2记录的t1（比如示例中的t1_id=4）不会被过滤
• 按t1.id分组，每个t1对应唯一分组
• HAVING条件统计每个分组内状态为S的记录数，等于0即代表该t1没有关联过S状态的t2记录
• MAX(t2.status)利用状态字典序CG>PG的特性，直接取到t1对应的最新有效状态

方案2：反连过滤写法（更直观，不需要分组） #

SELECT DISTINCT
  t1.id AS t1_id,
  t2.status
FROM t1
LEFT JOIN t2 ON t1.id = t2.t1_id
-- 额外左连状态为S的t2记录
LEFT JOIN t2 t2_s ON t1.id = t2_s.t1_id AND t2_s.status = 'S'
-- 过滤掉存在S状态关联的t1
WHERE t2_s.id IS NULL;

逻辑解释 #

• 第二次左连接 t2 t2_s只会匹配到关联过S状态的t1记录
• WHERE t2_s.id IS NULL直接排除所有匹配到S状态的t1，剩下的就是符合要求的记录
• DISTINCT用于避免同一个t1关联多条非S状态t2时返回重复数据

结果验证 #

两种写法都可以得到预期结果：

• 返回t1_id=3，对应status为CG
• 返回t1_id=4，对应status为null

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