如何在SQL中筛选出对应value列含0到3全部取值的id相关记录

实现方案 #

假设你的表名为your_table，可替换为实际表名。

方法1：子查询关联（兼容所有SQL引擎）

先通过分组聚合筛选出符合条件的id，再关联原表取所有对应记录：

SELECT t.id, t.value
FROM your_table t
INNER JOIN (
    SELECT id
    FROM your_table
    WHERE value IN (0,1,2,3)
    GROUP BY id
    HAVING COUNT(DISTINCT value) = 4
) qualified_ids ON t.id = qualified_ids.id

如果同一个id下的value没有重复值，可以去掉COUNT里的DISTINCT，提升查询性能。

方法2：窗口函数（支持MySQL 8.0+、PostgreSQL、Hive、Spark SQL等现代SQL引擎）

无需关联，单趟扫描即可完成：

SELECT id, value
FROM (
    SELECT 
        id,
        value,
        COUNT(DISTINCT CASE WHEN value IN (0,1,2,3) THEN value END) OVER(PARTITION BY id) AS value_cnt
    FROM your_table
) tmp
WHERE value_cnt = 4

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