时间戳

首页 > 解决方案 > 如何为此类 sql 查询编写循环

sql - 如何为此类 sql 查询编写循环

问题描述

我需要编写这样的查询：

SELECT CAST (date_time_column AS DATE)  as 'Date',
 AVG(CASE WHEN FORMAT(date_time_column, 'HH:mm') = '00:00' then my_values ELSE NULL end) as '00:00',
 ........
 AVG(CASE WHEN FORMAT(date_time_column, 'HH:mm') = '23:59' then my_values ELSE NULL end) as '23:59'
 FROM table
 where date_time_column > '2021-08-12'
 GROUP BY CAST (date_time_column AS DATE)

有什么方法可以避免在查询中写 1440 行？

标签： sqlsql-servertsql

解决方案

您要做的是以行而不是列的形式返回数据。这是每分钟的一行，而不是每分钟的一列。

试试这样的：

WITH 
cteNums AS
(
    Select  TOP (1440) ROW_NUMBER() OVER(order by (Select Null)) - 1 as num
    From    sys.all_columns
    Order By num
)
, cteMinutes AS 
(
    Select  FORMAT(CAST(num/cast(1440.0 as real) as DATETIME), N'HH:mm') as Minutes
    From cteNums
)
select  CAST(t.date_time_column AS DATE)  as 'Date',
        m.Minutes,
        AVG(CASE WHEN FORMAT(t.date_time_column, 'HH:mm') = m.Minute then t.my_values ELSE NULL end) as AvgValues
FROM cteMinutes m
LEFT JOIN [table] t   ON m.Minutes = FORMAT(t.date_time_column, 'HH:mm')
where t.date_time_column > '2021-08-12'
GROUP BY CAST(t.date_time_column AS DATE), m.Minutes
ORDER BY CAST(t.date_time_column AS DATE), m.Minutes
;

当然，我不能对此进行测试，因为没有提供测试数据或表定义。

推荐阅读