SQL查询实现Silo Wt列空值显示0并正确计算Total列总和

• 当Silo Wt字段值为NULL时，该位置显示为0
• Total列正确展示两个权重字段的数值之和，Silo Wt为NULL时也能正常计算；实现Silo列空值返回0后，Total列计算逻辑无需额外调整

原SQL中统计Silo重量的子查询在没有匹配记录时，SUM()聚合函数会返回NULL，NULL参与算术运算时整个计算结果都会变为NULL，最终导致Silo列显示空值、Total列计算错误。

你当前的SQL是SQL Server语法（表名使用方括号界定），直接使用SQL Server内置的ISNULL(待处理值, 0)函数，将Silo重量统计结果的NULL值转为0即可同时满足两个需求，修改后的可直接运行代码如下：

SELECT DISTINCT coffee_type AS "Coffee_Type",
    (SELECT ItemName
     FROM [T01_Item_Name_TBL]
     WHERE Item = B.Coffee_Type) AS "Description",
    (SELECT COUNT(Green_Inventory_ID)
     FROM [Green_Inventory] AS A
     WHERE A.Coffee_Type = B.Coffee_Type
       AND current_Quantity > 0) AS "Current Units",
    SUM(Unit_Weight * Current_Quantity) AS "Green Inv Wt",
    ISNULL((SELECT SUM(TGWeight)
     FROM [P04_Green_STotal_TBL] AS C
     WHERE TGItem = Coffee_type), 0) AS "Silo Wt",
    SUM(Unit_Weight * Current_Quantity) + 
      ISNULL((SELECT SUM(TGWeight)
       FROM [P04_Green_STotal_TBL] AS C
       WHERE TGItem = Coffee_type), 0) AS Total
FROM 
    [Green_Inventory] AS B
WHERE 
    Pallet_Status = 0
GROUP BY 
    Coffee_Type

原代码重复编写了两次完全相同的Silo重量统计子查询，会重复执行计算，可读性和性能都有优化空间，可以通过OUTER APPLY提前计算Silo重量，避免重复写子查询：

SELECT 
    B.coffee_type AS "Coffee_Type",
    (SELECT ItemName FROM [T01_Item_Name_TBL] WHERE Item = B.Coffee_Type) AS "Description",
    (SELECT COUNT(Green_Inventory_ID) FROM [Green_Inventory] AS A WHERE A.Coffee_Type = B.Coffee_Type AND current_Quantity > 0) AS "Current Units",
    SUM(B.Unit_Weight * B.Current_Quantity) AS "Green Inv Wt",
    ISNULL(C.SiloWt, 0) AS "Silo Wt",
    SUM(B.Unit_Weight * B.Current_Quantity) + ISNULL(C.SiloWt, 0) AS Total
FROM [Green_Inventory] AS B
OUTER APPLY (
    SELECT SUM(TGWeight) AS SiloWt
    FROM [P04_Green_STotal_TBL]
    WHERE TGItem = B.Coffee_type
) AS C
WHERE B.Pallet_Status = 0
GROUP BY B.Coffee_Type, C.SiloWt

跨数据库适配说明：如果使用其他数据库，仅需要替换空值处理函数即可：MySQL环境用IFNULL(表达式, 0)，PostgreSQL、Oracle等支持ANSI SQL的数据库用COALESCE(表达式, 0)，核心逻辑完全一致。

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