SQL Server中GROUP BY语句未正常工作的原因排查

我执行了如下SQL查询：

SELECT TOP 500
        BusinessDate,
        BRANCH_CO_MNE,
        RIGHT(TRANS_INPUTTER, 5) 'USER_ID',
        CASE
            WHEN TRANS_TYPE LIKE '%Deposit%'
            THEN COUNT(*)
                END 'No of Cash Deposit'
FROM    test_link.MMBL_phase2.dbo.EB_MMBL_H_UAR_PROT
WHERE   BusinessDate = '2023-01-23'
GROUP BY    BusinessDate,
            BRANCH_CO_MNE,
            TRANS_INPUTTER,
            TRANS_TYPE
ORDER BY    USER_ID

实际返回结果 #

预期结果 #

请问为何GROUP BY未正常工作？

原因分析 #

你的GROUP BY子句包含了TRANS_TYPE，这意味着每个不同的交易类型都会被单独分组：

• 对于TRANS_TYPE包含Deposit的记录，会统计该类型下的行数并返回数值；
• 对于每个不同的非Deposit类型，都会生成一行独立的分组，此时CASE条件不满足，返回NULL。
  这就是同一USER_ID出现多条NULL结果的核心原因——该用户操作了多种非Deposit类型的交易，每种类型对应一行分组结果。

另外，GROUP BY中使用的是TRANS_INPUTTER而非查询中生成的USER_ID，虽然当前数据中RIGHT(TRANS_INPUTTER,5)得到的USER_ID相同，但如果存在TRANS_INPUTTER前缀不同但后缀5位相同的情况，也会导致不必要的分组。

修正方案 #

方案1：按USER_ID聚合，统计每个用户的现金存款总数

如果想得到每个用户的现金存款总数量，无存款则显示NULL，可修改查询如下：

SELECT TOP 500
        BusinessDate,
        BRANCH_CO_MNE,
        RIGHT(TRANS_INPUTTER, 5) AS USER_ID,
        SUM(CASE WHEN TRANS_TYPE LIKE '%Deposit%' THEN 1 ELSE 0 END) AS 'No of Cash Deposit'
FROM    test_link.MMBL_phase2.dbo.EB_MMBL_H_UAR_PROT
WHERE   BusinessDate = '2023-01-23'
GROUP BY    BusinessDate,
            BRANCH_CO_MNE,
            RIGHT(TRANS_INPUTTER, 5) -- 改用生成的USER_ID分组，避免TRANS_INPUTTER前缀干扰
ORDER BY    USER_ID

方案2：区分Deposit/非Deposit类型，合并同类型分组

如果想保留用户的Deposit和非Deposit类型区分，且同一类型只显示一行结果，可调整分组逻辑：

SELECT TOP 500
        BusinessDate,
        BRANCH_CO_MNE,
        RIGHT(TRANS_INPUTTER, 5) AS USER_ID,
        CASE WHEN TRANS_TYPE_GROUP = 'Deposit' THEN COUNT(*) END AS 'No of Cash Deposit'
FROM (
    SELECT 
        BusinessDate,
        BRANCH_CO_MNE,
        TRANS_INPUTTER,
        CASE WHEN TRANS_TYPE LIKE '%Deposit%' THEN 'Deposit' ELSE 'Non-Deposit' END AS TRANS_TYPE_GROUP
    FROM test_link.MMBL_phase2.dbo.EB_MMBL_H_UAR_PROT
    WHERE BusinessDate = '2023-01-23'
) t
GROUP BY    BusinessDate,
            BRANCH_CO_MNE,
            RIGHT(TRANS_INPUTTER, 5),
            TRANS_TYPE_GROUP
ORDER BY    USER_ID, TRANS_TYPE_GROUP DESC

内容的提问来源于stack exchange，提问作者K H A N