如何筛选仅含指定TransactionCode的账户并分组统计交易数

要筛选出仅使用Code3和Code4的账户并分组统计，直接用WHERE子句无法满足需求（它仅过滤单条记录，无法排除存在其他交易码的账户），需先识别出符合条件的账户ID，再基于这些ID查询统计数据。

方法一：子查询筛选合格账户 #

SELECT Acct.Id, Acct.TransactionCode, COUNT(*) AS Cnt
FROM Account Acct
WHERE Acct.Id IN (
    SELECT Id
    FROM Account
    GROUP BY Id
    -- 确保该账户所有交易码都属于目标范围
    HAVING SUM(CASE WHEN TransactionCode NOT IN ('Code3', 'Code4') THEN 1 ELSE 0 END) = 0
    -- 若要求账户必须同时拥有Code3和Code4，添加此行
    -- AND COUNT(DISTINCT TransactionCode) = 2
)
GROUP BY Acct.Id, Acct.TransactionCode

方法二：窗口函数标记不合格账户 #

WITH AccountCodes AS (
    SELECT 
        Id,
        TransactionCode,
        -- 标记账户是否存在非目标交易码
        MAX(CASE WHEN TransactionCode NOT IN ('Code3', 'Code4') THEN 1 ELSE 0 END) OVER (PARTITION BY Id) AS HasOtherCode
    FROM Account
)
SELECT Id, TransactionCode, COUNT(*) AS Cnt
FROM AccountCodes
WHERE HasOtherCode = 0
GROUP BY Id, TransactionCode

执行结果 #

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