SQL Server：如何将循环生成的多个单行结果合并为单表？

我有一份按分钟记录数据的数据集，需要统计指定月份内每日的列平均值。目前编写的循环查询能得到正确结果，但返回的是31个各含1行的独立结果集，而非1个包含31行的单表。请问该现象的原因是什么？如何将这些结果合并为单表？

当前使用的查询代码：

SET NOCOUNT ON;
DECLARE @StartDate DateTime
SET @StartDate = dateadd(day,datediff(day,1,getdate()),0) -- 11.12.2023

DECLARE @EndDate DateTime
SET @EndDate = dateadd(day,datediff(day,0,getdate()),0) --11.13.2023

DECLARE @CurrentMonthStart DateTime
SET @CurrentMonthStart = dateadd(month,datediff(month,0,getdate()),0) -- 11.01.2023 stop date for last month
       
DECLARE @LastMonthStart DateTime
SET @LastMonthStart = dateadd(month,-1,@CurrentMonthStart +1) -- 10.02.2023 start with first day of month

DECLARE @EndDateCycle DateTime
SET @EndDateCycle = dateadd(day,1, @LastMonthStart) -- 10.03.2023 reading available full day after

-- Inflow

SET @StartDate = @LastMonthStart -- 10.02.2023
SET @EndDate = @EndDateCycle -- 10.03.2023
WHILE @StartDate <= @CurrentMonthStart -- check to make sure we're still in last month
    BEGIN
        DECLARE @AP Float
        SET @AP = (SELECT
              Avg(AP_INF_FT01MGD_VAL0) --this gets one value, average of the minutely data 
        FROM MINUTELY
        WHERE timestamp >= @StartDate --to loop forward in month, day to day
        AND timestamp < @EndDate)
    SELECT 'Apollo' = @AP,
        'Reading Date' = @StartDate
    WHERE @AP IS NOT NULL -- had to add because I'd get an extra reading with NULL (investigate)
    SET @StartDate = @StartDate + 1 --plus 1 to loop through the month
    SET @EndDate    = @EndDate + 1
    END

实际返回31个独立单行表，预期是包含31行的单表。

问题原因 #

你当前的循环逻辑中，每次循环都会执行一次SELECT语句，每执行一次SELECT就会返回一个独立的结果集。循环跑31次，自然就输出31个单独的单行结果集。

解决方法 #

方法1：修改循环逻辑，用临时表合并结果 #

在循环前创建临时表存储每次计算的结果，循环结束后一次性查询临时表内容，就能得到单表结果：

SET NOCOUNT ON;
DECLARE @StartDate DateTime
SET @StartDate = dateadd(day,datediff(day,1,getdate()),0) -- 11.12.2023

DECLARE @EndDate DateTime
SET @EndDate = dateadd(day,datediff(day,0,getdate()),0) --11.13.2023

DECLARE @CurrentMonthStart DateTime
SET @CurrentMonthStart = dateadd(month,datediff(month,0,getdate()),0) -- 11.01.2023 stop date for last month
       
DECLARE @LastMonthStart DateTime
SET @LastMonthStart = dateadd(month,-1,@CurrentMonthStart +1) -- 10.02.2023 start with first day of month

DECLARE @EndDateCycle DateTime
SET @EndDateCycle = dateadd(day,1, @LastMonthStart) -- 10.03.2023 reading available full day after

-- 创建临时表存储结果
CREATE TABLE #DailyAverages (
    Apollo FLOAT,
    [Reading Date] DATETIME
)

-- Inflow
SET @StartDate = @LastMonthStart -- 10.02.2023
SET @EndDate = @EndDateCycle -- 10.03.2023
WHILE @StartDate <= @CurrentMonthStart -- check to make sure we're still in last month
    BEGIN
        DECLARE @AP Float
        SET @AP = (SELECT
              Avg(AP_INF_FT01MGD_VAL0) --this gets one value, average of the minutely data 
        FROM MINUTELY
        WHERE timestamp >= @StartDate --to loop forward in month, day to day
        AND timestamp < @EndDate)
        
        -- 将结果插入临时表，而非直接输出
        INSERT INTO #DailyAverages (Apollo, [Reading Date])
        SELECT @AP, @StartDate
        WHERE @AP IS NOT NULL -- 过滤NULL值
        
        SET @StartDate = @StartDate + 1 --plus 1 to loop through the month
        SET @EndDate    = @EndDate + 1
    END

-- 一次性输出所有结果
SELECT Apollo, [Reading Date] FROM #DailyAverages

-- 删除临时表
DROP TABLE #DailyAverages

方法2：用分组查询替代循环（更高效） #

SQL里循环通常效率偏低，推荐直接按日期分组计算每日平均值，无需循环：

SET NOCOUNT ON;

DECLARE @CurrentMonthStart DateTime
SET @CurrentMonthStart = dateadd(month,datediff(month,0,getdate()),0) -- 11.01.2023
       
DECLARE @LastMonthStart DateTime
SET @LastMonthStart = dateadd(month,-1,@CurrentMonthStart +1) -- 10.02.2023

SELECT 
    Avg(AP_INF_FT01MGD_VAL0) AS Apollo,
    CAST(timestamp AS DATE) AS [Reading Date]
FROM MINUTELY
WHERE timestamp >= @LastMonthStart 
  AND timestamp < @CurrentMonthStart
GROUP BY CAST(timestamp AS DATE)
ORDER BY [Reading Date]

这个查询通过CAST(timestamp AS DATE)把分钟级数据按日期分组，直接计算每日平均值，返回的就是包含所有日期结果的单表，同时避免了循环带来的性能损耗。

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