如何查询无创建者的test_id？SQL语句错误排查及正确写法

表结构与数据 #

user_test_access表记录拥有测试访问权限的用户及测试创建者，结构及数据如下：

需求说明 #

需要返回所有无创建者的test_id，即该test_id对应的所有行中test_creator均为0，预期结果为test_id 1和2（test_id 3存在test_creator=1的记录，不符合要求）。

错误语句分析 #

你尝试的语句逻辑完全偏离需求：

SELECT test_id from user_test_access WHERE id = ALL(SELECT id from user_test_access WHERE test_creator=0)

• ALL子查询返回的是所有test_creator=0的行的id集合（即1、2、3、4、5、7）
• 主查询要求当前行的id等于所有这些值，这在逻辑上不可能实现（一个id只能是单个数值），因此该语句不会返回任何结果。

正确查询方法 #

方法1：GROUP BY + HAVING（推荐，逻辑直观） #

通过分组test_id，判断分组内的test_creator最大值是否为0（若存在test_creator=1，最大值会是1）：

SELECT test_id
FROM user_test_access
GROUP BY test_id
HAVING MAX(test_creator) = 0;

方法2：NOT EXISTS（适用于复杂关联场景） #

查找不存在test_creator=1记录的test_id：

SELECT DISTINCT test_id
FROM user_test_access uta1
WHERE NOT EXISTS (
    SELECT 1
    FROM user_test_access uta2
    WHERE uta2.test_id = uta1.test_id
      AND uta2.test_creator = 1
);

方法3：LEFT JOIN 排除法 #

先找出所有有创建者的test_id，再从总集合中排除这些值：

SELECT DISTINCT uta.test_id
FROM user_test_access uta
LEFT JOIN (
    SELECT DISTINCT test_id
    FROM user_test_access
    WHERE test_creator = 1
) creators ON uta.test_id = creators.test_id
WHERE creators.test_id IS NULL;

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