SQL子查询列求和异常求助：Total列计算结果错误

错误原因 #

Total列的计算逻辑存在问题：每个用于求和的子查询若没有匹配数据会返回NULL，而NULL与任何数值相加结果仍为NULL，最终外层的COALESCE会把整个NULL结果转换为0.00。但Current、First等列已经通过COALESCE将单个子查询的NULL转为0.00，因此直接复用这些列求和是最可靠且高效的方式。

解决方案1：复用已计算列求和（推荐） #

直接使用前面已处理好的Current、First、Second、Third、Last列进行求和，避免重复执行子查询，同时确保所有值都是非NULL的有效数值：

修改后的完整查询 #

SELECT a.seed, a.bill_address,
  COALESCE(
    CAST((SELECT SUM(balance) 
     FROM [dbo].[bill_transaction2] 
     WHERE transaction_id IN 
       (SELECT transaction_id 
        FROM [dbo].[bill_transaction1] 
        WHERE start_date = '07-18-2023' AND customer_id = a.seed)
    ) AS DECIMAL(18, 2)), 0.00
  ) AS 'Current',
  COALESCE(
    CAST((SELECT SUM(balance) 
     FROM [dbo].[bill_transaction2] 
     WHERE transaction_id IN 
       (SELECT transaction_id 
        FROM [dbo].[bill_transaction1] 
        WHERE start_date BETWEEN '07-19-2023' AND '08-18-2023' AND customer_id = a.seed)
    ) AS DECIMAL(18, 2)), 0.00
  ) AS 'First',
  COALESCE(
    CAST((SELECT SUM(balance) 
     FROM [dbo].[bill_transaction2] 
     WHERE transaction_id IN 
       (SELECT transaction_id 
        FROM [dbo].[bill_transaction1] 
        WHERE start_date BETWEEN '08-19-2023' AND '09-18-2023' AND customer_id = a.seed)
    ) AS DECIMAL(18, 2)), 0.00
  ) AS 'Second',
  COALESCE(
    CAST((SELECT SUM(balance) 
     FROM [dbo].[bill_transaction2] 
     WHERE transaction_id IN 
       (SELECT transaction_id 
        FROM [dbo].[bill_transaction1] 
        WHERE start_date BETWEEN '09-19-2023' AND '10-18-2023' AND customer_id = a.seed)
    ) AS DECIMAL(18, 2)), 0.00
  ) AS 'Third',
  COALESCE(
    CAST((SELECT SUM(balance) 
     FROM [dbo].[bill_transaction2] 
     WHERE transaction_id IN 
       (SELECT transaction_id 
        FROM [dbo].[bill_transaction1] 
        WHERE start_date BETWEEN '10-19-2023' AND '11-18-2023' AND customer_id = a.seed)
    ) AS DECIMAL(18, 2)), 0.00
  ) AS 'Last',
  -- 修改后的Total计算
  CAST(
    [Current] + [First] + [Second] + [Third] + [Last]
    AS DECIMAL(18, 2)
  ) AS 'Total'
FROM [dbo].[customers] AS a
WHERE a.active = 1

解决方案2：修正子查询的NULL处理 #

若坚持使用子查询计算Total，需为每个子查询的SUM结果单独添加COALESCE转换为0.00，避免出现NULL导致整个求和结果为NULL：

修改后的Total部分代码 #

COALESCE(
  CAST(
    COALESCE((SELECT SUM(balance) 
         FROM [dbo].[bill_transaction2] 
         WHERE transaction_id IN 
           (SELECT transaction_id 
            FROM [dbo].[bill_transaction1] 
            WHERE start_date = '07-18-2023' AND customer_id = a.seed)
        ), 0.00)
    + 
    COALESCE((SELECT SUM(balance) 
         FROM [dbo].[bill_transaction2] 
         WHERE transaction_id IN 
           (SELECT transaction_id 
            FROM [dbo].[bill_transaction1] 
            WHERE start_date BETWEEN '07-19-2023' AND '08-18-2023' AND customer_id = a.seed)
        ), 0.00)
    + 
    COALESCE((SELECT SUM(balance) 
         FROM [dbo].[bill_transaction2] 
         WHERE transaction_id IN 
           (SELECT transaction_id 
            FROM [dbo].[bill_transaction1] 
            WHERE start_date BETWEEN '08-19-2023' AND '09-18-2023' AND customer_id = a.seed)
        ), 0.00)
    + 
    COALESCE((SELECT SUM(balance) 
         FROM [dbo].[bill_transaction2] 
         WHERE transaction_id IN 
           (SELECT transaction_id 
            FROM [dbo].[bill_transaction1] 
            WHERE start_date BETWEEN '09-19-2023' AND '10-18-2023' AND customer_id = a.seed)
        ), 0.00)
    + 
    COALESCE((SELECT SUM(balance) 
         FROM [dbo].[bill_transaction2] 
         WHERE transaction_id IN 
           (SELECT transaction_id 
            FROM [dbo].[bill_transaction1] 
            WHERE start_date BETWEEN '10-19-2023' AND '11-18-2023' AND customer_id = a.seed)
        ), 0.00)
    AS DECIMAL(18, 2)
  ), 0.00
) AS 'Total'

注：此方案会重复执行多次子查询，性能不如方案1。

正确输出结果 #

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