CASE WHEN查询返回行数异常问题求助（SQL Server）

我尝试使用基础CASE WHEN语句分析MarketingAnalytics.dbo.Ri_PA_Personal表，统计各风险区间的客户数量，并按HighlyLikelyIndicator（Y/N）进一步拆分。但SQL Server查询返回了大量同一风险区间的重复行：预期2个风险区间对应4行结果（每个区间分Y/N），实际却得到多行重复的A1a、A1b等区间数据。

返回结果示例：

使用的SQL代码：

select count(distinct involvedPartyId),
case when MachineLearningScores >= 934.50 THEN 'A1a'
when MachineLearningScores >= 920.50 and MachineLearningScores <= 934.49 THEN 'A1b'
when MachineLearningScores >= 907.50 and MachineLearningScores <= 920.49 THEN 'A1c'
when MachineLearningScores >= 895.50 and MachineLearningScores <= 907.49 THEN 'A1d'
when MachineLearningScores >= 882.50 and MachineLearningScores <= 895.49 THEN 'A1e'
when MachineLearningScores >= 869.50 and MachineLearningScores <= 882.49 THEN 'A1f'
when MachineLearningScores >= 859.50 and MachineLearningScores <= 869.49 THEN 'A1g'
when MachineLearningScores >= 848.50 and MachineLearningScores <= 859.49 THEN 'A1h'
when MachineLearningScores >= 839.50 and MachineLearningScores <= 848.49 THEN 'A1i'
when MachineLearningScores >= 830.50 and MachineLearningScores <= 839.49 THEN 'A2'
when MachineLearningScores >= 809.50 and MachineLearningScores <= 830.49 THEN 'A3'
when MachineLearningScores >= 796.50 and MachineLearningScores <= 809.49 THEN 'A4'
when MachineLearningScores >= 789.50 and MachineLearningScores <= 796.49 THEN 'A5'
when MachineLearningScores >= 767.50 and MachineLearningScores <= 789.49 THEN 'B1'
when MachineLearningScores >= 752.50 and MachineLearningScores <= 767.49 THEN 'B2'
when MachineLearningScores >= 733.50 and MachineLearningScores <= 752.49 THEN 'B3'
when MachineLearningScores >= 724.50 and MachineLearningScores <= 733.49 THEN 'C1'
when MachineLearningScores >= 685.50 and MachineLearningScores <= 724.49 THEN 'D1'
when MachineLearningScores >= 649.50 and MachineLearningScores <= 685.49 THEN 'E1'
when MachineLearningScores < 649.49 THEN 'F1'
ELSE 'No MachineLearningScores'    
END AS 'SRIRiskBand', 
HighlyLikelyIndicator
from MarketingAnalytics.dbo.Ri_PA_Personal
where batchid = (SELECT MAX(BatchId) FROM MarketingAnalytics.dbo.Ri_PA_Personal)
group by MachineLearningScores, HighlyLikelyIndicator
order by MachineLearningScores asc

问题原因 #

查询的GROUP BY子句使用了MachineLearningScores字段，这会导致每个不同的分数值都会生成单独的分组，而非按照CASE语句生成的风险区间分组。即使多个分数属于同一个风险区间，只要分数值不同，就会被拆分成不同的组，最终返回重复的风险区间行。

修复后的SQL代码 #

select 
    count(distinct involvedPartyId) as 客户数量,
    SRIRiskBand,
    HighlyLikelyIndicator
from (
    select 
        involvedPartyId,
        HighlyLikelyIndicator,
        case 
            when MachineLearningScores >= 934.50 THEN 'A1a'
            when MachineLearningScores >= 920.50 and MachineLearningScores <= 934.49 THEN 'A1b'
            when MachineLearningScores >= 907.50 and MachineLearningScores <= 920.49 THEN 'A1c'
            when MachineLearningScores >= 895.50 and MachineLearningScores <= 907.49 THEN 'A1d'
            when MachineLearningScores >= 882.50 and MachineLearningScores <= 895.49 THEN 'A1e'
            when MachineLearningScores >= 869.50 and MachineLearningScores <= 882.49 THEN 'A1f'
            when MachineLearningScores >= 859.50 and MachineLearningScores <= 869.49 THEN 'A1g'
            when MachineLearningScores >= 848.50 and MachineLearningScores <= 859.49 THEN 'A1h'
            when MachineLearningScores >= 839.50 and MachineLearningScores <= 848.49 THEN 'A1i'
            when MachineLearningScores >= 830.50 and MachineLearningScores <= 839.49 THEN 'A2'
            when MachineLearningScores >= 809.50 and MachineLearningScores <= 830.49 THEN 'A3'
            when MachineLearningScores >= 796.50 and MachineLearningScores <= 809.49 THEN 'A4'
            when MachineLearningScores >= 789.50 and MachineLearningScores <= 796.49 THEN 'A5'
            when MachineLearningScores >= 767.50 and MachineLearningScores <= 789.49 THEN 'B1'
            when MachineLearningScores >= 752.50 and MachineLearningScores <= 767.49 THEN 'B2'
            when MachineLearningScores >= 733.50 and MachineLearningScores <= 752.49 THEN 'B3'
            when MachineLearningScores >= 724.50 and MachineLearningScores <= 733.49 THEN 'C1'
            when MachineLearningScores >= 685.50 and MachineLearningScores <= 724.49 THEN 'D1'
            when MachineLearningScores >= 649.50 and MachineLearningScores <= 685.49 THEN 'E1'
            when MachineLearningScores < 649.49 THEN 'F1'
            ELSE 'No MachineLearningScores'    
        END AS SRIRiskBand
    from MarketingAnalytics.dbo.Ri_PA_Personal
    where batchid = (SELECT MAX(BatchId) FROM MarketingAnalytics.dbo.Ri_PA_Personal)
) as subquery
group by SRIRiskBand, HighlyLikelyIndicator
order by 
    case SRIRiskBand
        when 'A1a' then 1
        when 'A1b' then 2
        when 'A1c' then 3
        when 'A1d' then 4
        when 'A1e' then 5
        when 'A1f' then 6
        when 'A1g' then 7
        when 'A1h' then 8
        when 'A1i' then 9
        when 'A2' then 10
        when 'A3' then 11
        when 'A4' then 12
        when 'A5' then 13
        when 'B1' then 14
        when 'B2' then 15
        when 'B3' then 16
        when 'C1' then 17
        when 'D1' then 18
        when 'E1' then 19
        when 'F1' then 20
        when 'No MachineLearningScores' then 21
    end asc

关键修改点 #

1. 子查询生成风险区间：先通过子查询为每条记录计算对应的SRIRiskBand，避免在GROUP BY中直接使用原始分数字段。
2. 分组字段调整：外层查询按SRIRiskBand和HighlyLikelyIndicator分组，确保同一风险区间+同一Indicator的记录被合并成一行。
3. 排序优化：使用CASE语句定义风险区间的排序逻辑，避免按字符串默认排序导致的顺序混乱（比如A10会排在A2前面）。

内容的提问来源于stack exchange，提问作者J O'Donnell