SQL查询需求：筛选重复ID中Status=0且No≠0的记录

原表数据 #

期望结果 #

筛选规则 #

仅当ID存在重复值时，选取其中Status=0且No≠0的记录；若ID为唯一值则不显示。

实现SQL #

方法一：使用EXISTS子查询 #

SELECT t.ID, t.No, t.Status
FROM your_table t
WHERE t.Status = 0 
  AND t.No != 0
  AND EXISTS (
    SELECT 1 
    FROM your_table t2 
    WHERE t2.ID = t.ID 
    GROUP BY t2.ID 
    HAVING COUNT(*) > 1
  );

逻辑：先通过子查询定位所有出现次数大于1的ID，再在主查询中筛选符合Status和No条件的记录。

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

WITH id_count AS (
  SELECT 
    ID, No, Status,
    COUNT(*) OVER (PARTITION BY ID) AS id_occurrence
  FROM your_table
)
SELECT ID, No, Status
FROM id_count
WHERE id_occurrence > 1
  AND Status = 0
  AND No != 0;

逻辑：用窗口函数统计每个ID的出现次数，再过滤出重复ID且满足状态、编号条件的记录。

内容的提问来源于stack exchange，提问作者Mr.ED