如何简化多条件SQL查询并实现传感器数据列转行合并？

需要从服务器检索并排序数据，现有两条SQL查询语句冗长，需简化优化；当前每个传感器类型返回独立结果表，希望合并为单个表，以DateTime、CelNr为公共列，每个传感器对应一列，并按DateTime倒序获取各传感器最新的Top2数据。现有查询代码如下：

SELECT TOP(2)
    [clRegId] AS Id, [clRegDateTimestamp] AS DateTime, 
    ([clRegSysNbr] + 1) AS CelNr, 
    ((([clRegValue] - 4860.00)/20.00) - 30.00) AS Value,
    CASE
        WHEN clRegId = 0 THEN 'airTemp'
    END AS Sensor
FROM 
    [Database].[dbo].[registrations] 
WHERE 
    clRegSysNbr = 10 
    AND clRegId = 0 
    AND clRegMain = 3 
    AND clRegConfigId = 3
ORDER BY 
    DateTimestamp DESC


SELECT TOP(2)
    [clRegId] AS Id, [clRegDateTimestamp] AS DateTime, 
    ([clRegSysNbr] + 1) AS CelNr, 
    ([clRegValue] / 40.0) AS Value,
    CASE
        WHEN clRegId = 1 THEN 'product cold'
    END AS Sensor
FROM 
    [Database].[dbo].[registrations] 
WHERE 
    clRegSysNbr = 10 
    AND clRegId = 1 
    AND clRegMain = 3 
    AND clRegConfigId = 3
ORDER BY 
    DateTimestamp DESC

当前执行后返回两个独立的传感器数据表，期望得到将各传感器数据按DateTime合并为单表的结果样式，请问能否通过SQL实现该需求？

完全可以通过SQL实现这个需求，我们可以用窗口函数先筛选出每个传感器的最新2条数据，再用条件聚合将不同传感器的值转成列，最终合并成单表。

优化后的SQL代码如下：

WITH RankedSensors AS (
    SELECT 
        clRegDateTimestamp AS DateTime,
        (clRegSysNbr + 1) AS CelNr,
        -- 根据传感器ID计算对应的值
        CASE clRegId
            WHEN 0 THEN (((clRegValue - 4860.00)/20.00) - 30.00)
            WHEN 1 THEN (clRegValue / 40.0)
        END AS SensorValue,
        CASE clRegId
            WHEN 0 THEN 'airTemp'
            WHEN 1 THEN 'product cold'
        END AS SensorName,
        -- 按传感器分组，按时间倒序排名，取Top2
        ROW_NUMBER() OVER (PARTITION BY clRegId ORDER BY clRegDateTimestamp DESC) AS rn
    FROM [Database].[dbo].[registrations]
    WHERE 
        clRegSysNbr = 10 
        AND clRegId IN (0,1)  -- 同时筛选两个传感器
        AND clRegMain = 3 
        AND clRegConfigId = 3
)
SELECT 
    DateTime,
    CelNr,
    -- 将不同传感器的值转成列
    MAX(CASE WHEN SensorName = 'airTemp' THEN SensorValue END) AS airTemp,
    MAX(CASE WHEN SensorName = 'product cold' THEN SensorValue END) AS [product cold]
FROM RankedSensors
WHERE rn <= 2  -- 只保留每个传感器的最新2条
GROUP BY DateTime, CelNr
ORDER BY DateTime DESC;

代码说明： #

• CTE（公用表表达式）RankedSensors：
  • 一次性筛选出两个传感器的所有符合条件的数据，避免重复写两次查询逻辑
  • 使用ROW_NUMBER()窗口函数，按clRegId分组，对每个传感器的数据按时间倒序排名，这样每个传感器的前2条数据会被标记为rn=1和rn=2
  • 统一处理不同传感器的值计算逻辑，简化代码
• 最终查询：
  • 通过条件聚合MAX(CASE ...)将不同传感器的值转换为独立列，实现单表展示
  • 按DateTime和CelNr分组，确保同一时间同一CelNr下的传感器值在同一行
  • 最后按DateTime倒序排序，符合获取最新数据的需求

如果你的数据库支持PIVOT语法，也可以用PIVOT实现列转换，但条件聚合的兼容性更好，适用于大多数SQL数据库（如SQL Server、MySQL、PostgreSQL等）。

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