如何在SQL查询中避免空值，显示0.00而非空白？

目前我编写了如下代码，期望当条件不满足时，查询结果显示0.00而非空白值。

ALTER PROCEDURE [dbo].[spComputeScDiscount] 
  @CustId int,
  @BillMonth nvarchar(50)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ROUND(CAST(TR.[Bill]*0.05 as numeric(18,3)),2)
  FROM [EltrFng].[dbo].[Transactions] TR
  JOIN Customer AS CS
    ON TR.CustomerId = CN.Id
  WHERE trans.[Particulars] = 'Billing' 
    AND TR.[CustomerId] = @CustId
    AND TR.[Billmonth] = @BillMonth 
    AND CN.Discount = 1
    AND TR.Consumption <= 30     
END

问题修正与实现方案 #

原代码存在两处语法错误：

• JOIN时给Customer表起的别名是CS，但后续引用时误用了CN，需统一为CS
• WHERE子句中误用了未定义的表别名trans，需改为Transactions表的别名TR

要实现“无匹配结果时返回0.00”的需求，可通过COALESCE函数处理查询结果——当子查询无匹配返回NULL时，自动替换为指定的0.00。修改后的完整代码如下：

ALTER PROCEDURE [dbo].[spComputeScDiscount] 
  @CustId int,
  @BillMonth nvarchar(50)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT COALESCE(
    (SELECT ROUND(CAST(TR.[Bill]*0.05 as numeric(18,3)),2)
     FROM [EltrFng].[dbo].[Transactions] TR
     JOIN Customer AS CS
       ON TR.CustomerId = CS.Id
     WHERE TR.[Particulars] = 'Billing' 
       AND TR.[CustomerId] = @CustId
       AND TR.[Billmonth] = @BillMonth 
       AND CS.Discount = 1
       AND TR.Consumption <= 30),
    0.00
  ) AS DiscountAmount
END

补充说明 #

如果原查询可能返回多行结果，建议在子查询中添加TOP 1或者使用SUM等聚合函数，确保子查询返回单一值，避免执行报错。

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