sql - 我如何使用 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。

这是我的 Full 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 是一个没有任何参数的函数生成的 View 。

Answer 1

使用合并

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