SQL Server中使用EXCEPT函数实现数值相减失败的问题

你误用了EXCEPT的功能——它是集合差集运算符，作用是返回第一个查询结果里存在、但第二个查询结果里没有的行，根本不是用来做数值减法的。你的两个查询针对同一个MeterServicePointNumber统计，只要两个结果的Generation数值不同，EXCEPT就会直接返回第一个查询的行，完全不会执行减法计算。

要实现"ChannelNumber='03'的统计值减去ChannelNumber='01'的统计值"，推荐用以下两种方法：

方法一：条件聚合（推荐，效率更高） #

通过CASE语句分别统计两个渠道的总和，直接在查询中完成减法：

SELECT 
  SUM(CASE WHEN ChannelNumber = '03' THEN IntervalUsageQuantity ELSE 0 END) 
  - SUM(CASE WHEN ChannelNumber = '01' THEN IntervalUsageQuantity ELSE 0 END) AS Generation,
  MeterServicePointNumber
FROM BillingCustomer.IntervalHourly
WHERE IntervalYear = '2024' 
  AND IntervalMonth = '10' 
  AND MeterServicePointNumber = '693834004-00002'
  AND ChannelNumber IN ('01', '03') -- 只过滤需要的渠道，减少计算量
GROUP BY MeterServicePointNumber

方法二：子查询直接相减 #

如果两个查询的过滤条件差异较大，可以用独立子查询分别计算后再相减：

SELECT 
  (SELECT SUM(IntervalUsageQuantity) 
   FROM BillingCustomer.IntervalHourly
   WHERE IntervalYear = '2024' 
     AND IntervalMonth = '10' 
     AND ChannelNumber = '03' 
     AND MeterServicePointNumber = '693834004-00002')
  -
  (SELECT SUM(IntervalUsageQuantity) 
   FROM BillingCustomer.IntervalHourly
   WHERE IntervalYear = '2024' 
     AND IntervalMonth = '10' 
     AND ChannelNumber = '01' 
     AND MeterServicePointNumber = '693834004-00002') AS Generation,
  '693834004-00002' AS MeterServicePointNumber

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