SQL Server 2016中如何更简洁获取指定顺序的三行温度数据？

嘿，我来帮你简化这个获取关键温度节点的CTE逻辑！针对SQL Server 2016，咱们可以用窗口函数把原来繁琐的第三行获取方式变得更简洁易读，而且性能也更优。

先明确场景假设 #

我先基于常见的温度数据场景补全表结构（如果你的表结构不同，只需对应调整字段即可）：

CREATE TABLE TemperatureData (
    ProcessId INT, -- 假设按不同流程分组，若单流程可去掉
    ReadingTime DATETIME2(3), -- 温度读取时间（按此排序）
    Temperature INT, -- 测量温度值
    -- 其他业务字段...
);

优化后的完整代码 #

DECLARE @ThresholdMinTemp INT = 550;
DECLARE @ThresholdPostTemp INT = 300; -- 替换为你实际需要的第二个阈值

WITH KeyTemperatureMarks AS (
    SELECT 
        ProcessId,
        ReadingTime,
        Temperature,
        -- 标记每个流程的起始首行
        ROW_NUMBER() OVER (PARTITION BY ProcessId ORDER BY ReadingTime) AS RowNum,
        -- 标记第一次达到Min阈值的行
        CASE 
            WHEN Temperature >= @ThresholdMinTemp 
            AND ROW_NUMBER() OVER (
                PARTITION BY ProcessId 
                ORDER BY CASE WHEN Temperature >= @ThresholdMinTemp THEN 1 ELSE 2 END, ReadingTime
            ) = 1 
            THEN 1 
            ELSE 0 
        END AS IsFirstMinThreshold,
        -- 记录每个流程首次达到Min阈值的时间（用于后续筛选第三行）
        MIN(CASE WHEN Temperature >= @ThresholdMinTemp THEN ReadingTime END) OVER (PARTITION BY ProcessId) AS FirstMinThresholdTime
    FROM TemperatureData
),
PostThresholdFilter AS (
    SELECT 
        ProcessId,
        ReadingTime,
        Temperature,
        -- 筛选Min阈值之后首次达到第二个阈值的行
        ROW_NUMBER() OVER (
            PARTITION BY ProcessId 
            ORDER BY CASE WHEN ReadingTime > FirstMinThresholdTime AND Temperature <= @ThresholdPostTemp THEN 1 ELSE 2 END, ReadingTime
        ) AS PostRowRank
    FROM KeyTemperatureMarks
)
-- 合并三行结果并排序
SELECT 
    ProcessId,
    ReadingTime,
    Temperature,
    CASE
        WHEN RowNum = 1 THEN '流程起始首行'
        WHEN IsFirstMinThreshold = 1 THEN '达到Min阈值行'
    END AS EventDescription
FROM KeyTemperatureMarks
WHERE RowNum = 1 OR IsFirstMinThreshold = 1

UNION ALL

SELECT 
    ProcessId,
    ReadingTime,
    Temperature,
    '达到后续阈值行' AS EventDescription
FROM PostThresholdFilter
WHERE PostRowRank = 1 
    AND FirstMinThresholdTime IS NOT NULL -- 确保存在Min阈值行
ORDER BY ProcessId, ReadingTime;

优化点说明 #

• 避免繁琐嵌套：用窗口函数把关键节点的标记逻辑整合到CTE中，替代原来多次子查询/关联的写法，可读性大幅提升
• 精准定位关键行：通过ROW_NUMBER()结合条件排序（CASE WHEN ... THEN 1 ELSE 2 END），快速锁定符合条件的第一行，不需要额外的表扫描
• 复用计算结果：用MIN() OVER (PARTITION BY...)一次性获取每个流程首次达到Min阈值的时间，避免重复计算

如果你的场景不需要按流程分组（单流程数据），只需去掉所有PARTITION BY ProcessId的部分即可，代码依然可以正常运行。

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