SQL Server 如何在mssql中为相同的时间戳创建不同的列

avwztpqn 于 2023-02-07 发布在其他

关注(0)|答案(1)|浏览(120)

如果我运行这个SQL查询

SELECT *
    FROM [Trend].[Details]
    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)
        order by [Timestamp]

需要ms来完成。
如果我运行这个SQL查询

SELECT [Timestamp]
          ,[Value] '74'
          ,(SELECT [Value] FROM [Trend].[Details] d2 where d2.[Timestamp]=d1.[Timestamp] and d2.[SignalId]=63) '63'
          ,(SELECT [Value] FROM [Trend].[Details] d3 where d3.[Timestamp]=d1.[Timestamp] and d3.[SignalId]=119) '119'
          ,(SELECT [Value] FROM [Trend].[Details] d4 where d4.[Timestamp]=d1.[Timestamp] and d4.[SignalId]=80) '80'
          ,(SELECT [Value] FROM [Trend].[Details] d5 where d5.[Timestamp]=d1.[Timestamp] and d5.[SignalId]=101) '101'
          ,(SELECT [Value] FROM [Trend].[Details] d6 where d6.[Timestamp]=d1.[Timestamp] and d6.[SignalId]=99) '99'
          ,(SELECT [Value] FROM [Trend].[Details] d7 where d7.[Timestamp]=d1.[Timestamp] and d7.[SignalId]=109) '109'
          ,(SELECT [Value] FROM [Trend].[Details] d8 where d8.[Timestamp]=d1.[Timestamp] and d8.[SignalId]=124) '124'
          ,(SELECT [Value] FROM [Trend].[Details] d9 where d9.[Timestamp]=d1.[Timestamp] and d9.[SignalId]=120) '120'
          ,(SELECT [Value] FROM [Trend].[Details] d10 where d10.[Timestamp]=d1.[Timestamp] and d10.[SignalId]=117) '117'
      FROM [Trend].[Details] d1 with (nolock)
      where [SignalId]=74 and [Timestamp] between '2023-01-27 08:00' and '2023-01-31'
      order by [Timestamp]

需要1分钟以上才能完成。
这是相同的信息。只是显示在不同的列中并按时间戳分组。
我想把结果作为第二个查询，而把查询时间作为第一个查询。我怎样才能写出一个好的查询呢？

sql-server

来源：https://stackoverflow.com/questions/75284874/how-to-create-different-columns-for-the-same-timestamp-in-ms-sql

1条答案

按热度按时间

gwbalxhn1#

正如在评论中提到的，你最好在这里透视你的数据。虽然你 * 可以 * 使用PIVOT操作符，我（和许多其他人）发现条件聚合是“更好”的方式，因为它对它如何工作的限制要少得多：

SELECT [Timestamp],
       MAX(CASE SignalId WHEN 74  THEN [Value] END) AS [74],
       MAX(CASE SignalId WHEN 63  THEN [Value] END) AS [63],
       MAX(CASE SignalId WHEN 119 THEN [Value] END) AS [119],
       MAX(CASE SignalId WHEN 80  THEN [Value] END) AS [80],
       MAX(CASE SignalId WHEN 101 THEN [Value] END) AS [101],
       MAX(CASE SignalId WHEN 99  THEN [Value] END) AS [99],
       MAX(CASE SignalId WHEN 109 THEN [Value] END) AS [109],
       MAX(CASE SignalId WHEN 124 THEN [Value] END) AS [124],
       MAX(CASE SignalId WHEN 120 THEN [Value] END) AS [120],
       MAX(CASE SignalId WHEN 117 THEN [Value] END) AS [117]
FROM [Trend].[Details]
WHERE [Timestamp] BETWEEN '2023-01-27T08:00:00' AND '2023-01-31T00:00:00' --Switched to unambiguous literal date (and time) value
  AND [SignalId] IN (74,63,119,80,101,99,109,124,120,117)
GROUP BY [Timestamp]
ORDER BY [Timestamp];

赞(0）回复(0）举报 2023-02-07

我来回答

SQL Server 如何在mssql中为相同的时间戳创建不同的列

1条答案

相关问题

热门标签

最新问答