代码之家 › 专栏 › 技术社区 › Md Kamran Azam

如果sum在sql中导致0,如何在sql中使用sum函数返回blank

sum sql-server-2008 sql

Md Kamran Azam · 技术社区 · 7 年前

我正在使用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”。如何处理同样的问题。

2 回复 | 直到 7 年前

EzLo tumao kaixin 7 年前

首先,你不需要 ISNULL 背景值为 0 ( 添加的中性点 )在一个 SUM 聚合,作为 和 已经忽略 NULL 价值观。所以 SUM(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;

如果你不想重复 和 表达式:

;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

请记住,在这两种情况下,如果没有计算总和的记录,结果将是 无效的 .

编辑 :添加没有意义 '' 在你的内心 和 ,因为它已转换为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,

Barbaros Özhan 7 年前

试试这个(使用无空再次):

Select isnull(Sum(isnull(a,0)),0) from table_;

我用过 table_ 而不是 table ,因为 桌子 是保留关键字。

SQL Fiddle Demo