SQL Server 2008中Sum函数结果为0时如何返回空白？

嗨，这问题我之前也碰到过，咱们一步步拆解解决：

问题根源 #

你当前的CASE语句里ELSE用了''（空字符串），但SUM是针对数值类型的聚合函数，SQL Server会自动把空字符串转换成数值0——这就是为啥没匹配记录时，结果总是0而不是空白。

解决方案 #

咱们通过两步调整就能实现需求：

1. 把CASE语句里的ELSE改成NULL（SUM函数会忽略NULL值，没有匹配记录时SUM会直接返回NULL）；
2. 用NULLIF把SUM结果为0的情况转换成NULL，再用ISNULL把NULL转换成空白字符串''。

修改后的完整代码 #

SELECT 
    'RCOAuthorizer LMS', 
    '' AS Consumer_Loan, 
    '' AS Auto_Loan, 
    '' AS Credit_Card, 
    ISNULL(NULLIF(SUM(CASE WHEN sq2.loan_type = 'Loan Amendment' THEN sq2.user_count ELSE NULL END ), 0), '') AS Loan_Amendment, 
    ISNULL(NULLIF(SUM(CASE WHEN sq2.loan_type = 'Pre-Payment' THEN sq2.user_count ELSE NULL END ), 0), '') AS Pre_Payment, 
    ISNULL(NULLIF(SUM(CASE WHEN sq2.loan_type = 'Corporate Credit card' THEN sq2.user_count ELSE NULL END ), 0), '') AS Corporate_Credit_card, 
    '' AS Auto_Payment_Release, 
    '' AS Car_Mulkiya 
FROM ( 
    SELECT 
        'RCOAuthorizer' AS ws_name, 
        'Loan Amendment' AS loan_type, 
        COUNT(DISTINCT a.bpm_referenceno) AS user_count, 
        a.user_id AS user_id 
    FROM BM_LMS_DecisionHistoryGrid a 
    INNER JOIN ( 
        SELECT m.bpm_referenceno 
        FROM BM_LMS_EXTTABLE m 
        WHERE m.request_type = 'Loan Amendment' 
    ) sq1 ON a.bpm_referenceno = sq1.bpm_referenceno 
    WHERE workstep_name = 'RCOAuthorizer' 
    GROUP BY a.user_id 

    UNION 

    SELECT 
        'RCOAuthorizer', 
        'Pre-Payment', 
        COUNT(DISTINCT a.bpm_referenceno), 
        a.user_id 
    FROM BM_LMS_DecisionHistoryGrid a 
    INNER JOIN ( 
        SELECT m.bpm_referenceno 
        FROM BM_LMS_EXTTABLE m 
        WHERE m.request_type = 'Pre-Payment' 
    ) sq1 ON a.bpm_referenceno = sq1.bpm_referenceno 
    WHERE workstep_name = 'RCOAuthorizer' 
    GROUP BY a.user_id 

    UNION 

    SELECT 
        'RCOAuthorizer', 
        'Corporate Credit card', 
        COUNT(DISTINCT a.bpm_referenceno), 
        a.user_id 
    FROM BM_LMS_DecisionHistoryGrid a 
    INNER JOIN ( 
        SELECT m.bpm_referenceno 
        FROM BM_LMS_EXTTABLE m 
        WHERE m.request_type = 'Corporate Credit card' 
    ) sq1 ON a.bpm_referenceno = sq1.bpm_referenceno 
    WHERE workstep_name = 'RCOAuthorizer' 
    GROUP BY a.user_id 
) sq2 
GROUP BY sq2.ws_name

额外说明 #

• 如果你的SQL Server版本支持COALESCE，也可以用COALESCE(NULLIF(SUM(...), 0), '')，效果和ISNULL完全一致；
• 为啥要绕这一步？因为直接把SUM结果转字符串的话，0会变成'0'，而我们需要的是空字符串，所以先用NULLIF把0变成NULL，再替换成空白就完美符合需求了。

内容的提问来源于stack exchange，提问作者Md Kamran Azam