多SELECT语句联合执行返回空值但单独执行正常的问题排查（含循环与CASE语句）

你联合执行时#2返回NULL的核心原因是：#1的循环直接修改了@DespatchFrom变量，导致#2查询时用的起始日期已经超出了你的目标范围。

具体来说，你的循环里每跑一次就执行SET @DespatchFrom=DateAdd(d,1,@DespatchFrom)，当循环结束（@DespatchFrom超过@DespatchTo）时，@DespatchFrom的值已经变成了'2021-08-10'（也就是你原本的结束日期加1天）。这时候#2的查询条件t.DespatchDateTime >= (@DespatchFrom)就变成了t.DespatchDateTime >= '2021-08-10'，而结束日期还是'2021-08-09'，相当于查询一个空的日期区间，自然查不到任何数据，SUM()函数没有匹配行就会返回NULL。

方案1：保留循环，但用副本变量遍历 #

最简单的修复就是在循环前把原始的起始日期存到另一个变量里，循环用副本变量来迭代，这样原始的日期变量不会被污染：

Declare @DespatchFrom datetime = '2021-08-01'
Declare @DespatchTo datetime = '2021-08-09'
-- 新增变量保存原始起始日期，避免被循环修改
Declare @OriginalStartDate datetime = @DespatchFrom
Declare @SATcount int = 0
Declare @SUNcount int = 0
Declare @WKCount int = 0

-- #1 用副本变量@CurrentDate做循环遍历
Declare @CurrentDate datetime = @OriginalStartDate
while @CurrentDate<=@DespatchTo
Begin
    IF DATENAME(dw, @CurrentDate) = 'Saturday'
        SET @SATcount=@SATcount+1
    ELSE IF DATENAME(dw, @CurrentDate) = 'Sunday'
        SET @SUNcount=@SUNcount+1
    ELSE
        SET @WKCount=@WKCount+1
    SET @CurrentDate=DateAdd(d,1,@CurrentDate)
End
select @SATcount as SATCount, @SUNcount as SUNcount, @WKCount as WKCount

--#2 现在用原始的起始日期查询，范围正确了
Select SUM(CASE WHEN TranType = 'SRT' THEN (Net*-1) ELSE Net END) as SATNet
from trans t
where t.DespatchDateTime >= @OriginalStartDate 
and t.despatchDateTime <= DateAdd(d,1,@DespatchTo) 
-- 建议用DATENAME代替DatePart，避免DATEFIRST设置影响结果
and DATENAME(dw, t.Despatchdatetime) = 'Saturday'

--#3 计算平均净额，记得处理NULL情况
Declare @SATNet decimal(18,2)
Set @SATNet = ISNULL((Select SUM(CASE WHEN TranType = 'SRT' THEN (Net*-1) ELSE Net END) 
               from trans t
               where t.DespatchDateTime >= @OriginalStartDate 
               and t.despatchDateTime <= DateAdd(d,1,@DespatchTo) 
               and DATENAME(dw, t.Despatchdatetime) = 'Saturday'), 0)

Select CASE WHEN @SATcount = 0 THEN 0 ELSE @SATNet/@SATcount END as SATAverageNet

方案2：替换循环，用纯SQL实现（更高效） #

SQL里循环的效率通常不高，尤其是日期范围很大的时候，推荐用递归CTE生成日期列表，一次性完成天数统计和净额计算：

Declare @DespatchFrom datetime = '2021-08-01'
Declare @DespatchTo datetime = '2021-08-09'

-- 递归CTE生成日期范围内的所有日期，并标记星期几
;WITH DateRange AS (
    SELECT @DespatchFrom AS DespatchDate,
           DATENAME(dw, @DespatchFrom) AS DayOfWeek
    UNION ALL
    SELECT DateAdd(d,1,DespatchDate),
           DATENAME(dw, DateAdd(d,1,DespatchDate))
    FROM DateRange
    WHERE DespatchDate < @DespatchTo
),
-- 统计各类日期的数量
DayCounts AS (
    SELECT 
        COUNT(CASE WHEN DayOfWeek = 'Saturday' THEN 1 END) AS SATcount,
        COUNT(CASE WHEN DayOfWeek = 'Sunday' THEN 1 END) AS SUNcount,
        COUNT(CASE WHEN DayOfWeek NOT IN ('Saturday','Sunday') THEN 1 END) AS WKCount
    FROM DateRange
),
-- 统计各类日期的总净额
NetTotals AS (
    SELECT
        SUM(CASE WHEN DATENAME(dw, t.Despatchdatetime) = 'Saturday' THEN CASE WHEN TranType = 'SRT' THEN (Net*-1) ELSE Net END END) AS SATNet,
        SUM(CASE WHEN DATENAME(dw, t.Despatchdatetime) = 'Sunday' THEN CASE WHEN TranType = 'SRT' THEN (Net*-1) ELSE Net END END) AS SUNNet,
        SUM(CASE WHEN DATENAME(dw, t.Despatchdatetime) NOT IN ('Saturday','Sunday') THEN CASE WHEN TranType = 'SRT' THEN (Net*-1) ELSE Net END END) AS WKNet
    FROM trans t
    WHERE t.DespatchDateTime >= @DespatchFrom 
      AND t.despatchDateTime <= DateAdd(d,1,@DespatchTo)
)
-- 最终计算平均值，同时处理0除数和NULL的情况
SELECT
    SATcount,
    SUNcount,
    WKCount,
    CASE WHEN SATcount = 0 THEN 0 ELSE ISNULL(SATNet, 0)/SATcount END AS SATAverage,
    CASE WHEN SUNcount = 0 THEN 0 ELSE ISNULL(SUNNet, 0)/SUNcount END AS SUNAverage,
    CASE WHEN WKCount = 0 THEN 0 ELSE ISNULL(WKNet, 0)/WKCount END AS WKAverage
FROM DayCounts, NetTotals
OPTION (MAXRECURSION 0) -- 如果日期范围超过100天，必须加这个选项关闭递归限制

额外提醒 #

• DatePart("w", ...)的返回值会受SQL Server的SET DATEFIRST设置影响（比如默认DATEFIRST=7时，1代表周日，7代表周六），所以用DATENAME(dw, ...)直接判断星期几的字符串更稳妥，避免因服务器设置不同导致结果错误。
• 计算平均值时，一定要用ISNULL()把NULL的净额转为0，否则即使有天数，NULL/数字还是会返回NULL。

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