如何在T-SQL中按number列过滤同时含TAX与PAY的行？

需求说明 #

需要对包含number、ACCOUNT、COLOR列的数据表进行行过滤，规则如下：

• 若同一number对应的行中同时存在Account = 'TAX'和Account = 'PAY'，则排除该number的所有行
• 若同一number的行仅包含单一类型的Account（如全为TAX），则保留这些行

示例数据 #

原数据表 #

预期结果表 #

实现方案 #

方法一：窗口函数（高效推荐） #

利用窗口函数计算每个number分组内的唯一Account数量，仅保留数量为1的分组数据：

SELECT number, ACCOUNT, COLOR
FROM (
    SELECT 
        number, 
        ACCOUNT, 
        COLOR,
        COUNT(DISTINCT ACCOUNT) OVER (PARTITION BY number) AS account_type_count
    FROM your_table_name -- 替换为你的实际表名
) AS filtered
WHERE account_type_count = 1;

方法二：分组子查询关联 #

先筛选出仅含单一Account类型的number，再关联原表获取对应行：

SELECT t.number, t.ACCOUNT, t.COLOR
FROM your_table_name t
JOIN (
    SELECT number
    FROM your_table_name
    GROUP BY number
    -- 确保分组内要么没有TAX，要么没有PAY
    HAVING SUM(CASE WHEN ACCOUNT = 'TAX' THEN 1 ELSE 0 END) = 0
        OR SUM(CASE WHEN ACCOUNT = 'PAY' THEN 1 ELSE 0 END) = 0
) AS valid_numbers ON t.number = valid_numbers.number;

方法三：排除违规分组 #

直接排除同时包含TAX和PAY的number，查询剩余数据：

SELECT number, ACCOUNT, COLOR
FROM your_table_name
WHERE number NOT IN (
    SELECT number
    FROM your_table_name
    WHERE ACCOUNT IN ('TAX', 'PAY')
    GROUP BY number
    HAVING COUNT(DISTINCT ACCOUNT) = 2
);

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