SQL查询如何排除存在status icon为3的用户的全部记录

你之前使用的行级过滤条件WHERE status icon <> '3'仅会排除单条status为3的记录，不会将该用户的其他记录一同过滤，因此不符合按用户维度整体排除的需求，以下是几种可行的实现方案：

方案1：NOT IN 子查询（易理解，适合中小数据量） #

SELECT 
    name,
    `appt date`,
    `status icon`
FROM 
    替换为你的实际表名
WHERE 
    name NOT IN (
        SELECT DISTINCT name 
        FROM 替换为你的实际表名 
        WHERE `status icon` = '3'
    )

子查询先捞出所有存在status为3的用户名列表，外层直接排除这些用户的所有记录即可。

方案2：NOT EXISTS 关联子查询（性能更优，适合大数据量场景） #

SELECT 
    t1.name,
    t1.`appt date`,
    t1.`status icon`
FROM 
    替换为你的实际表名 t1
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM 替换为你的实际表名 t2 
        WHERE t2.name = t1.name 
        AND t2.`status icon` = '3'
    )

这种写法可以避免NOT IN在子查询返回空值时的异常问题，执行效率也更高。

方案3：窗口函数（适合需要同时保留用户维度统计信息的场景） #

WITH user_status_count AS (
    SELECT 
        *,
        COUNT(CASE WHEN `status icon` = '3' THEN 1 END) OVER (PARTITION BY name) AS status_3_cnt
    FROM 替换为你的实际表名
)
SELECT 
    name,
    `appt date`,
    `status icon`
FROM user_status_count
WHERE status_3_cnt = 0

以上三种写法返回的结果均符合你的预期，只会返回bob和karen的所有记录：

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