SUM 上的 NULL 值的 SQL 问题

Question

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

我有这个方法，可以查找现金交易，并取消现金返还，但有时没有现金交易，因此该值会变成 NULL，并且您无法从 NULL 中减去。 我尝试在它周围放置一个 ISNULL，但它仍然变成 null。

谁能帮我解决这个问题吗？

;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) 返回列表中的第一个非空参数。

Answer 2

尝试将 ISNULL 放入 SUM 和 ABS 中，即实际字段周围，如下所示

SUM(ISNULL(tp.payment_amount, 0))

SUM(ABS(ISNULL(tp.payment_amount, 0)))

Answer 3

我没有 MS SQL 可以在这里测试，但是将 ISNULL 放在 SELECT 周围是否有效？如果没有匹配的行，也许根本不会触发 ISNULL ...