SQL Server中按两列计数过滤行的查询结果不符问题

表结构与测试数据 #

建表语句：

CREATE TABLE x
(
    ColumnA CHAR NOT NULL
  , ColumnB CHAR NOT NULL
  , ColumnC INT NOT NULL
  , CONSTRAINT CompositeKey PRIMARY KEY (ColumnA, ColumnB)
);

插入测试数据：

INSERT INTO x
VALUES
 ('A', 'X', 2)
,('A', 'Y', 1)
,('B', 'X', 4)
,('B', 'Z', 2)
,('C', 'X', 9)
,('C', 'Y', 3)
,('C', 'P', 2)
,('D', 'X', 6)
,('D', 'Y', 4)
,('E', 'P', 2)

查询需求 #

结合预期结果，实际需要筛选满足以下逻辑的记录：

• 记录所属的ColumnA分组中，至少有2条记录的ColumnB出现次数>2
• 记录自身的ColumnB出现次数>2

原查询问题 #

原查询仅筛选单条记录满足ColumnA出现次数>1且ColumnB出现次数>2，未考虑该ColumnA下符合ColumnB条件的记录数量，导致保留了ColumnA='B'的('B','X',4)记录（该记录自身满足条件，但ColumnA='B'下仅有这1条符合ColumnB条件的记录）。

修正后的查询 #

以下是两种实现方式：

方式一：使用CTE分步筛选 #

WITH ValidColumnB AS (
    -- 先找出出现次数>2的ColumnB
    SELECT ColumnB
    FROM x
    GROUP BY ColumnB
    HAVING COUNT(*) > 2
),
ValidColumnA AS (
    -- 找出至少有2条记录属于ValidColumnB的ColumnA
    SELECT ColumnA
    FROM x
    WHERE ColumnB IN (SELECT ColumnB FROM ValidColumnB)
    GROUP BY ColumnA
    HAVING COUNT(*) > 1
)
-- 最终筛选符合条件的记录
SELECT ColumnA, ColumnB, ColumnC
FROM x
WHERE ColumnA IN (SELECT ColumnA FROM ValidColumnA)
  AND ColumnB IN (SELECT ColumnB FROM ValidColumnB);

方式二：使用窗口函数 #

SELECT ColumnA, ColumnB, ColumnC
FROM (
    SELECT *,
           -- 标记当前ColumnB的出现次数
           COUNT(*) OVER (PARTITION BY ColumnB) AS columnB_cnt,
           -- 统计当前ColumnA下，符合ColumnB条件的记录数
           COUNT(CASE WHEN COUNT(*) OVER (PARTITION BY ColumnB) > 2 THEN 1 END) 
               OVER (PARTITION BY ColumnA) AS valid_colB_count
    FROM x
) DS
WHERE columnB_cnt > 2
  AND valid_colB_count > 1;

结果验证 #

执行上述查询后，将得到预期结果：

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