使用COALESCE处理SUM减法仍返回NULL的SQL问题咨询

表结构与数据 #

我有三张表：UserOrdersActivity、Orders、AmountPaid，具体结构和数据如下：

UserOrdersActivity表 #

Orders表 #

AmountPaid表 #

问题描述 #

我需要关联这三张表，计算每个用户的订单总金额、2025年4月1日前的付款总额，以及截至该日期的欠款金额。但执行查询后，最后一行（UserID=4）的欠款金额为NULL，即使已经用COALESCE处理了付款总额的NULL情况。

当前查询结果：

注：原查询存在字段名错误——将AmountPaid表的DatePaid写成了DateReceived，这会导致部分付款数据未被正确统计，比如UserID=2的付款金额应为20而非5。

原SQL语句 #

SELECT a.UserID,      
    SUM(b.[OrderPrice]) AS [Total Fees], 
    COALESCE(SUM(c.[AmountPaid]),0) AS [Amount Paid Before April 1st], 
    SUM(b.[OrderPrice]) - sum(c.[AmountPaid]) AS [Total Owning on Date Apr 1st]
FROM Orders b 
INNER JOIN UserOrdersActivity a ON a.OrderID = b.OrderID
LEFT OUTER JOIN AmountPaid c  ON a.UserID = c.UserID 
    AND c.DateReceived <= '2025-04-01' -- 此处字段名错误，应为DatePaid
GROUP BY a.UserID
ORDER BY [UserID]

解决方法 #

方法1：在减法运算中对付款总额聚合结果使用COALESCE #

问题核心：COALESCE仅处理了显示字段的NULL，但减法运算中的SUM(c.[AmountPaid])仍为NULL，任何数值与NULL运算结果都是NULL。需在减法时也将NULL转为0：

SELECT a.UserID,      
    SUM(b.[OrderPrice]) AS [Total Fees], 
    COALESCE(SUM(c.[AmountPaid]), 0) AS [Amount Paid Before April 1st], 
    SUM(b.[OrderPrice]) - COALESCE(SUM(c.[AmountPaid]), 0) AS [Total Owning on Date Apr 1st]
FROM Orders b 
INNER JOIN UserOrdersActivity a ON a.OrderID = b.OrderID
LEFT OUTER JOIN AmountPaid c  ON a.UserID = c.UserID 
    AND c.DatePaid <= '2025-04-01' -- 修正字段名错误
GROUP BY a.UserID
ORDER BY [UserID]

方法2：用子查询预计算用户付款总额 #

先通过子查询统计每个用户在指定日期前的付款总额，再与订单数据关联，从源头避免NULL值干扰：

WITH UserPayments AS (
    SELECT 
        UserID,
        COALESCE(SUM(AmountPaid), 0) AS PaidBeforeApr1
    FROM AmountPaid
    WHERE DatePaid <= '2025-04-01'
    GROUP BY UserID
)
SELECT 
    a.UserID,
    SUM(b.OrderPrice) AS [Total Fees],
    COALESCE(up.PaidBeforeApr1, 0) AS [Amount Paid Before April 1st],
    SUM(b.OrderPrice) - COALESCE(up.PaidBeforeApr1, 0) AS [Total Owning on Date Apr 1st]
FROM Orders b
INNER JOIN UserOrdersActivity a ON a.OrderID = b.OrderID
LEFT JOIN UserPayments up ON a.UserID = up.UserID
GROUP BY a.UserID, up.PaidBeforeApr1
ORDER BY a.UserID

方法3：使用ISNULL函数（适用于SQL Server） #

若使用SQL Server，可替换COALESCE为ISNULL，实现相同效果：

SELECT a.UserID,      
    SUM(b.[OrderPrice]) AS [Total Fees], 
    ISNULL(SUM(c.[AmountPaid]), 0) AS [Amount Paid Before April 1st], 
    SUM(b.[OrderPrice]) - ISNULL(SUM(c.[AmountPaid]), 0) AS [Total Owning on Date Apr 1st]
FROM Orders b 
INNER JOIN UserOrdersActivity a ON a.OrderID = b.OrderID
LEFT OUTER JOIN AmountPaid c  ON a.UserID = c.UserID 
    AND c.DatePaid <= '2025-04-01'
GROUP BY a.UserID
ORDER BY [UserID]

内容的提问来源于stack exchange，提问作者GorillaForce