SUM 上的 NULL 值的 SQL 问题

Question

我目前正在处理一些 sql 的东西，但运行时遇到了一些问题。

我有这个方法来查找现金交易，并取消现金返还，但有时没有现金交易，因此该值变为 NULL 并且您不能从 NULL 中减去。
我试图在它周围放置一个 ISNULL，但它仍然变成空值。

谁能帮我这个？

;WITH tran_payment AS
(
SELECT 1 AS payment_method, NULL AS payment_amount, null as tran_header_cid
UNION ALL
SELECT 998 AS payment_method, 2 AS payment_amount, NULL as tran_header_cid
), 
paytype AS
(
SELECT 1 AS mopid, 2 AS mopshort
),
tran_header AS
(
SELECT 1 AS cid
)
            SELECT p.mopid                     AS mopid,
                   p.mopshort                  AS descript,
                   payment_value AS PaymentValue,  
                   ISNULL(DeclaredValue, 0.00) AS DeclaredValue
            from   paytype p
                   LEFT OUTER JOIN (SELECT CASE 
                       When (tp.payment_method = 1) 
                       THEN
                     (ISNULL(SUM(tp.payment_amount), 0)
                     - (SELECT ISNULL(SUM(ABS(tp.payment_amount)), 0)
                           FROM tran_payment tp
                           INNER JOIN tran_header th on tp.tran_header_cid = th.cid
        WHERE payment_method = 998
        ) )
     ELSE SUM(tp.payment_amount)
     END as payment_value,
     tp.payment_method,
     0   as DeclaredValue
     FROM   tran_header th
     LEFT OUTER JOIN tran_payment tp
     ON tp.tran_header_cid = th.cid
     GROUP  BY payment_method) pmts
     ON p.mopid = pmts.payment_method  

Answer 1

也许 COALESCE()能帮你？

你可以试试这个:

SUM(COALESCE(tp.payment_amount, 0))

或者

COALESCE(SUM(tp.payment_amount), 0)

COALESCE(arg1, arg2, ..., argN)返回列表中的第一个非空参数。