如何在MS SQL中高效实现同时间戳多列展示并优化查询速度

你的问题核心是行转列时用了大量关联子查询，每个子查询都会对表做一次独立扫描，导致总耗时剧增。下面是两种高效写法，性能和你的第一个基础查询持平：

方法1：使用SQL Server专属的PIVOT函数 #

这是最简洁高效的写法，仅需一次扫描目标数据集：

SELECT [Timestamp], [74], [63], [119], [80], [101], [99], [109], [124], [120], [117]
FROM (
    SELECT [Timestamp], [SignalId], [Value]
    FROM [Trend].[Details] WITH (NOLOCK)
    WHERE [Timestamp] BETWEEN '2023-01-27 08:00' AND '2023-01-31'
        AND [SignalId] IN (74,63,119,80,101,99,109,124,120,117)
) AS SourceData
PIVOT (
    MAX([Value]) -- 同一Timestamp+SignalId仅一条数据时，MAX/MIN/SUM均可
    FOR [SignalId] IN ([74], [63], [119], [80], [101], [99], [109], [124], [120], [117])
) AS PivotTable
ORDER BY [Timestamp];

方法2：条件聚合（通用所有SQL数据库） #

如果你的数据库不支持PIVOT，用条件聚合同样高效，原理是一次扫描后通过CASE语句转列：

SELECT 
    [Timestamp],
    MAX(CASE WHEN [SignalId] = 74 THEN [Value] END) AS [74],
    MAX(CASE WHEN [SignalId] = 63 THEN [Value] END) AS [63],
    MAX(CASE WHEN [SignalId] = 119 THEN [Value] END) AS [119],
    MAX(CASE WHEN [SignalId] = 80 THEN [Value] END) AS [80],
    MAX(CASE WHEN [SignalId] = 101 THEN [Value] END) AS [101],
    MAX(CASE WHEN [SignalId] = 99 THEN [Value] END) AS [99],
    MAX(CASE WHEN [SignalId] = 109 THEN [Value] END) AS [109],
    MAX(CASE WHEN [SignalId] = 124 THEN [Value] END) AS [124],
    MAX(CASE WHEN [SignalId] = 120 THEN [Value] END) AS [120],
    MAX(CASE WHEN [SignalId] = 117 THEN [Value] END) AS [117]
FROM [Trend].[Details] WITH (NOLOCK)
WHERE [Timestamp] BETWEEN '2023-01-27 08:00' AND '2023-01-31'
    AND [SignalId] IN (74,63,119,80,101,99,109,124,120,117)
GROUP BY [Timestamp]
ORDER BY [Timestamp];

性能优化补充 #

确保你的表上已经创建复合索引，让查询直接命中索引避免全表扫描：

CREATE NONCLUSTERED INDEX IX_TrendDetails_Timestamp_SignalId ON [Trend].[Details] ([Timestamp], [SignalId]) INCLUDE ([Value]);

内容的提问来源于stack exchange，提问作者Björn Granbom