SQL查询：筛选对应多行且无value为'aaa'的ID

原始数据表 #

筛选需求 #

• 目标ID对应的数据行数量大于1
• 目标ID的所有value值均不等于'aaa'
  预期结果仅为ID=2

方法1：GROUP BY + HAVING 组合（通用写法） #

这是最直观的实现方式，通过分组统计完成双重条件筛选：

SELECT id
FROM your_table_name
GROUP BY id
HAVING COUNT(*) > 1 
   AND SUM(CASE WHEN value = 'aaa' THEN 1 ELSE 0 END) = 0;

逻辑说明：

• COUNT(*) > 1：确保当前ID的总行数大于1
• SUM(CASE...) = 0：统计该ID下value为'aaa'的行数，等于0则说明无任何一行符合该值

方法2：NOT EXISTS 子查询 #

通过子查询排除包含'aaa'的ID，再结合行数校验：

SELECT DISTINCT t1.id
FROM your_table_name t1
WHERE NOT EXISTS (
    SELECT 1 
    FROM your_table_name t2 
    WHERE t2.id = t1.id AND t2.value = 'aaa'
)
AND (SELECT COUNT(*) FROM your_table_name t3 WHERE t3.id = t1.id) > 1;

逻辑说明：

• NOT EXISTS子查询直接排除所有存在value='aaa'的ID
• 后续子查询确保剩余ID的总行数大于1

方法3：窗口函数（适用于MySQL 8+、PostgreSQL等支持窗口函数的数据库） #

先通过窗口函数预计算每个ID的统计指标，再筛选结果：

WITH id_stats AS (
    SELECT 
        id,
        COUNT(*) OVER(PARTITION BY id) AS row_count,
        MAX(CASE WHEN value = 'aaa' THEN 1 ELSE 0 END) OVER(PARTITION BY id) AS has_aaa
    FROM your_table_name
)
SELECT DISTINCT id
FROM id_stats
WHERE row_count > 1 AND has_aaa = 0;

逻辑说明：

• 窗口函数COUNT(*) OVER(PARTITION BY id)计算每个ID的总行数
• MAX(CASE...) OVER(PARTITION BY id)标记该ID是否存在'aaa'（1为存在，0为不存在）
• 最后筛选出行数大于1且无'aaa'的ID

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