sql - 如果 Sum 在 SQL 中结果为 0，如何在 Sql 中使用 Sum 函数返回“空白”

Question

我正在使用 Sql Server 2008。我正在使用 Sum 函数添加一些列值。就像下面的代码：

 SELECT 'RCOAuthorizer LMS',
'' AS Consumer_Loan,
'' AS Auto_Loan,
'' AS Credit_Card,
SUM(CASE
       WHEN sq2.loan_type = 'Loan Amendment' THEN sq2.user_count
       ELSE ''
   END ) AS Loan_Amendment,
SUM(CASE
       WHEN sq2.loan_type = 'Pre-Payment' THEN sq2.user_count
       ELSE ''
   END ) AS Pre_Payment,
SUM(CASE
       WHEN sq2.loan_type = 'Corporate Credit card' THEN sq2.user_count
       ELSE ''
   END ) 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

上面的查询将返回“a”列中所有可用数字的总和。但是如果没有记录，那么它会返回 '0' 作为结果。

我要求如果没有记录，它必须显示空白而不是显示“0”。怎么处理一样。

标签： sqlsql-server-2008sum

Answer 1

首先，您不需要在聚合中ISNULL返回值为0（添加的中性）的值，因为它已经忽略了值。所以等于（但不同于！）。SUMSUMNULLSUM(ISNULL(Column, 0))SUM(Column)ISNULL(SUM(Column), 0)

似乎你想要一个VARCHAR结果而不是一个数字。您可以使用CASE.

Select 
    CASE WHEN Sum(a) = 0 THEN '' ELSE CONVERT(VARCHAR(100), Sum(a)) END
from 
    table;

如果您不想重复SUM表达式：

;WITH SumResult AS
(
    Select CONVERT(VARCHAR(100), Sum(a)) AS SumTotal 
    from table
)
SELECT
    CASE WHEN R.SumTotal = '0' THEN '' ELSE R.SumTotal END
FROM
    SumResult AS R

请记住，在这两种情况下，如果没有记录来计算总和，则结果将为NULL.

---

编辑''：在 your中添加没有意义SUM，因为它已转换为 0 才能求和。解决方案仍然与我之前发布的相同。

改变

SUM(CASE
       WHEN sq2.loan_type = 'Pre-Payment' THEN sq2.user_count
       ELSE ''
   END ) AS Pre_Payment,

为了

CASE 
    WHEN SUM(CASE WHEN sq2.loan_type = 'Pre-Payment' THEN sq2.user_count END) = 0 THEN ''
    ELSE CONVERT(VARCHAR(100), SUM(CASE WHEN sq2.loan_type = 'Pre-Payment' THEN sq2.user_count)) END AS Pre_Payment,