如何使用CASE-WHEN将空值列显示为0？

Question

您好，我的一个字段为空，我正在尝试使用distinct来过滤掉重复的冗余和记录。这是我的SQL语句，它根据绘制和未绘制来计算校验值！

SELECT   CASE SUM(Money)
             WHEN null THEN sum(0)
             ELSE SUM(Money)
         END AS MoneyTaken
FROM     (SELECT   Money
          FROM     tblPayment AS tblPayment_1
          WHERE    (CheckStatus = 0) AND (IDBuyer = @BuyerID) AND (IDSource = @SourceID)
          GROUP BY ID, IDBuyer, IDSeller, Money) AS derivedtbl_1_1) AS [Cheques Which didnt have credit], tblPurchase.Price AS SellPrice

但是这不起作用，在执行函数时，我仍然在MoneyTaken列中得到NULL。

这是我的完整StoredProcedure，它仍然显示重复的结果：

ALTER PROCEDURE [dbo].[SGetListofKharIDBuyer]
@SurceId bigint,
@BuyerId bigint
AS
    SET NOCOUNT ON;
    /*
    0 = check(cheque)  dosent have credit-so couldntget money out of it
    1 = check(cheque) has credit- got the money
   -1 = in cashe  - paid in cash
    */

SELECT     distinct   vPatmentForReport.Account, vPatmentForReport.PaymentStatus, vPatmentForReport.Description, vPatmentForReport.Bank, vPatmentForReport.Serial, 
                         vPatmentForReport.PaymentMethod, vPatmentForReport.Date, vPatmentForReport.مبلغ * Money AS [Payment in Cash], tblPurchase.Date AS [PurchaseDate], 
                         tblReserve.Fee , tblReserve.NumberOfReserve , tblReserve.TotalMoney ,
                             (SELECT         COALESCE( SUM(Money),0) AS [PaidMoney]
                                FROM            (SELECT        Money
                                                           FROM            tblPardakht
                                                           WHERE        (CheckStatus <> 0) AND (IDBuyer = @BuyerId) AND (IDSource = @SurceId)
                                                           GROUP BY ID, IDBuyer, IDAccount, Money) AS derivedtbl_1) AS [Sum of Cheques and in cash payments],
                             (SELECT       COALESCE( SUM(Money),0) AS [PaidMoney]
                                FROM            (SELECT        Money
                                                           FROM            tblPardakht AS tblPardakht_1
                                                           WHERE        (CheckStatus = 0) AND (IDBuyer = @BuyerId) AND (IDSource = @SurceId)
                                                           GROUP BY ID, IDBuyer, IDAccount, Money) AS derivedtbl_1_1) AS [sum of cheques which are not drawn], tblPurchase.Money , 
                         tblReserve.Description , tblReserve.Date
FROM            tblPurchase INNER JOIN
                         tblReserve ON tblPurchase.IDReserve = tblReserve.ID inner JOIN
                         vPatmentForReport ON tblReserve.IDSource = vPatmentForReport.[SourceId] AND tblReserve.IDBuyer = vPatmentForReport.[BuyerId]
WHERE        (vPatmentForReport.[BuyerId] = @BuyerId) AND (vPatmentForReport.[SourceId] = @SurceId)

vPatmentForReport是一个不带任何参数的函数生成的视图。

Answer 1

使用COALESCE

SELECT COALESCE(SUM(Money),0), ....

Answer 2

试试下面的代码：

SELECT   SUM(Money) as  MoneyTaken
FROM     (SELECT   Money
          FROM     tblPayment AS tblPayment_1
          WHERE    (CheckStatus = 0) AND (IDBuyer = @BuyerID) AND (IDSource = @SourceID) and ISNULL(Money,0)<> 0
          GROUP BY ID, IDBuyer, IDSeller, Money) AS derivedtbl_1_1) AS [Cheques Which didnt have credit], tblPurchase.Price AS SellPrice

Answer 3

如果子选择不返回任何行，就会得到NULL，在子选择之外使用ISNULL :例如

,ISNULL(
(SELECT SUM(Money) AS [PaidMoney]
FROM (SELECT  Money
FROM tblPardakht 
WHERE (CheckStatus <> 0) AND (IDBuyer = @BuyerId) AND (IDSource = @SurceId)
GROUP BY ID, IDBuyer, IDAccount, Money) AS derivedtbl_1) 
,0) AS [Sum of Cheques and in cash payments]