如何编写SQL获取自身及所有关联views.status均为1的tasks记录

基础信息 #

现有两张业务表：

• tasks（任务表）
  • id：主键
  • status：任务状态字段
• views（视图记录表）
  • id：主键
  • taskid：关联tasks.id的外键
  • status：视图记录状态字段

测试数据如下：

1. tasks表存在记录：id=1、status=1
2. views表存在2条关联taskid=1的记录：
   • 记录1：id=1、taskid=1、status=1
   • 记录2：id=2、taskid=1、status=0

查询要求：返回同时满足以下两个条件的task记录：

• tasks.status = 1
• 该task关联的所有views记录的status均为1，只要存在任意一条关联view的status不等于1，就排除该task。

原SQL错误原因 #

原SQL写法：

SELECT tasks.id FROM tasks JOIN views ON tasks.id = views.taskid WHERE tasks.status = 1 AND views.status = 1;

该写法的逻辑缺陷：内连接加views.status=1的过滤条件，只会筛掉status不等于1的单条view记录，只要task存在至少一条status=1的关联view，就会返回对应task，无法排除「同时存在status非1的关联view」的情况，因此会错误返回id=1的task记录。

可落地的正确写法 #

写法1：NOT EXISTS 反查（推荐，性能最优） #

逻辑最直白，通过子查询排除存在不符合条件关联view的task：

SELECT t.id
FROM tasks t
WHERE t.status = 1
  AND NOT EXISTS (
      SELECT 1
      FROM views v
      WHERE v.taskid = t.id
        AND v.status <> 1
  );

执行逻辑：遍历status=1的task，只要能找到任意一条关联的view的status不是1，就排除该task，最终留下的就是所有关联view状态全为1的task。

写法2：GROUP BY + 聚合校验 #

通过分组聚合统计关联view的不合格记录数：

SELECT t.id
FROM tasks t
INNER JOIN views v ON t.id = v.taskid
WHERE t.status = 1
GROUP BY t.id
HAVING SUM(v.status <> 1) = 0;

如果业务中status只有0和1两个取值，也可以把HAVING子句替换为HAVING MIN(v.status) = 1，执行效率更高。

写法3：LEFT JOIN 反连接 #

和NOT EXISTS逻辑一致，通过左连接不合格view记录，筛选无匹配的task：

SELECT t.id
FROM tasks t
LEFT JOIN views v 
  ON v.taskid = t.id 
  AND v.status <> 1
WHERE t.status = 1
  AND v.id IS NULL;

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