如何使用SQL将查询结果中的空值或NULL转换为0？

针对你需要将查询结果中空或NULL的数值转换为0的需求，我们可以通过两个方向优化你的SQL：一是对所有可能返回NULL的计算字段用ISNULL()或COALESCE()包裹，二是通过条件聚合替代重复的UNION查询，提升执行效率。

修改后的SQL代码 #

DECLARE @ReportDate AS Date
SET @ReportDate = '2022-07-31'
DECLARE @Props AS int
SET @Props = 707

WITH AggregatedData AS (
    SELECT
        SUM(intOccupied) AS sumOccupied,
        SUM(intVacant) AS sumVacant,
        SUM(intDamaged) AS sumDamaged,
        SUM(dblOccupiedArea) AS sumOccupiedArea,
        SUM(dblVacantArea) AS sumVacantArea,
        SUM(dblDamagedArea) AS sumDamagedArea,
        SUM(mnyOccupiedPotential) AS sumOccupiedPotential,
        SUM(mnyVacantPotential) AS sumVacantPotential,
        SUM(mnyDamagedPotential) AS sumDamagedPotential,
        SUM(mnyOccupiedRent) AS sumOccupiedRent
    FROM tblUBMReport
    LEFT JOIN tblUBMInsuranceShare ON tblUBMInsuranceShare.intProp = tblUBMReport.intProp
    WHERE tblUBMReport.intProp IN (@Props) AND dtReport = @ReportDate
)
SELECT
    'Occupied' AS strType,
    ISNULL(sumOccupied, 0) AS intUnits,
    ISNULL(sumOccupiedArea, 0) AS dblSqFt,
    ISNULL(sumOccupiedPotential, 0) AS mnyGPI,
    ISNULL(sumOccupiedRent, 0) AS mnyOccRent,
    -- 处理分母为0的情况，避免除零错误
    CASE WHEN ISNULL(sumOccupied + sumVacant + sumDamaged, 0) = 0 THEN 0 
         ELSE ISNULL(sumOccupied, 0) / CAST(sumOccupied + sumVacant + sumDamaged AS float) 
    END AS OccupiedPercent,
    CASE WHEN ISNULL(sumOccupiedArea + sumVacantArea + sumDamagedArea, 0) = 0 THEN 0 
         ELSE ISNULL(sumOccupiedArea, 0) / CAST(sumOccupiedArea + sumVacantArea + sumDamagedArea AS float) 
    END AS SQFTPercent,
    -- 平均面积处理空值
    CASE WHEN ISNULL(sumOccupied, 0) = 0 THEN 0 
         ELSE ISNULL(sumOccupiedArea, 0) / sumOccupied 
    END AS mnyAverageSize,
    CASE WHEN ISNULL(sumOccupiedPotential + sumVacantPotential + sumDamagedPotential, 0) = 0 THEN 0 
         ELSE ISNULL(sumOccupiedRent, 0) / CAST(sumOccupiedPotential + sumVacantPotential + sumDamagedPotential AS float) 
    END AS mnyECONOcc,
    -- 租金差异处理空值
    CASE WHEN ISNULL(sumOccupiedPotential, 0) = 0 THEN 0 
         ELSE ISNULL(sumOccupiedRent, 0) / sumOccupiedPotential 
    END AS OccupiedVariance,
    CASE WHEN ISNULL(sumOccupiedPotential, 0) = 0 OR ISNULL(sumOccupiedPotential + sumVacantPotential + sumDamagedPotential, 0) = 0 THEN 0 
         ELSE (ISNULL(sumOccupiedRent, 0) / sumOccupiedPotential) / CAST(sumOccupiedPotential + sumVacantPotential + sumDamagedPotential AS float) 
    END AS OccupiedVariancePercent,
    1 AS intOrder
FROM AggregatedData
UNION ALL
SELECT
    'Vacant' AS strType,
    ISNULL(sumVacant, 0) AS intUnits,
    ISNULL(sumVacantArea, 0) AS dblSqFt,
    ISNULL(sumVacantPotential, 0) AS mnyGPI,
    NULL AS mnyOccRent, -- 业务上该字段为空，保留NULL
    CASE WHEN ISNULL(sumOccupied + sumVacant + sumDamaged, 0) = 0 THEN 0 
         ELSE ISNULL(sumVacant, 0) / CAST(sumOccupied + sumVacant + sumDamaged AS float) 
    END AS VaccantPercent,
    CASE WHEN ISNULL(sumOccupiedArea + sumVacantArea + sumDamagedArea, 0) = 0 THEN 0 
         ELSE ISNULL(sumVacantArea, 0) / CAST(sumOccupiedArea + sumVacantArea + sumDamagedArea AS float) 
    END AS SQFTPercent,
    NULL AS mnyVacantECON, -- 业务上该字段为空，保留NULL
    CASE WHEN ISNULL(sumVacant, 0) = 0 THEN 0 
         ELSE ISNULL(sumVacantArea, 0) / sumVacant 
    END AS mnyAverageSize,
    NULL AS mnyVariance, -- 业务上该字段为空，保留NULL
    NULL AS mnyVacantVarPercentage, -- 业务上该字段为空，保留NULL
    2 AS intOrder
FROM AggregatedData
UNION ALL
SELECT
    'Damaged' AS strType,
    ISNULL(sumDamaged, 0) AS intUnits,
    ISNULL(sumDamagedArea, 0) AS dblSqFt,
    ISNULL(sumDamagedPotential, 0) AS mnyGPI,
    NULL AS mnyOccRent, -- 业务上该字段为空，保留NULL
    NULL AS mnyDamageECON, -- 业务上该字段为空，保留NULL
    CASE WHEN ISNULL(sumOccupied + sumVacant + sumDamaged, 0) = 0 THEN 0 
         ELSE ISNULL(sumDamaged, 0) / CAST(sumOccupied + sumVacant + sumDamaged AS float) 
    END AS DamagedPercent,
    CASE WHEN ISNULL(sumOccupiedArea + sumVacantArea + sumDamagedArea, 0) = 0 THEN 0 
         ELSE ISNULL(sumDamagedArea, 0) / CAST(sumOccupiedArea + sumVacantArea + sumDamagedArea AS float) 
    END AS SQFTPercent,
    CASE WHEN ISNULL(sumDamaged, 0) = 0 THEN 0 
         ELSE ISNULL(sumDamagedArea, 0) / sumDamaged 
    END AS mnyAverageSize,
    NULL AS mnyVariance, -- 业务上该字段为空，保留NULL
    NULL AS mnyDamagedVarPercentage, -- 业务上该字段为空，保留NULL
    3 AS intOrder
FROM AggregatedData
ORDER BY intOrder

关键修改说明 #

• 引入CTE预聚合数据：避免三次重复查询同一张表，减少数据库IO开销，提升查询效率。
• 处理所有可能的NULL值：
  • 对求和后的字段用ISNULL(字段, 0)确保基础数值不为NULL
  • 对除法计算增加CASE判断，避免除零错误（原SQL未处理分母为0的情况，会触发报错）
  • 对mnyAverageSize这类依赖分子分母的字段，当分母为0时返回0，避免产生NULL
• 区分业务空值和计算空值：对于业务逻辑上应该为NULL的字段（如Vacant类型的mnyOccRent），保留NULL；对于计算产生的空值，统一转换为0。

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