590
社区成员
发帖
与我相关
我的任务
分享
;WITH cte AS(
SELECT DATEADD(minute,sv.number*5, Convert(CHAR(10),GETDATE(),120)) AS d
FROM [master].dbo.spt_values AS sv
WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 287
UNION ALL
SELECT DATEADD(minute,sv.number*5, Convert(CHAR(10),GETDATE()-1,120)) AS d
FROM [master].dbo.spt_values AS sv
WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 287
--构建两天的数据
)
,cte2 AS (
SELECT MAX(d) AS d FROM cte WHERE d<GETDATE()
)
,t1 AS(
SELECT ROW_NUMBER() OVER (ORDER BY d DESC) AS rid,*
FROM cte
WHERE d BETWEEN DATEADD(hour,-8,(SELECT d FROM cte2)) AND (SELECT d FROM cte2)
)
SELECT d
FROM t1
WHERE (rid-1)%6=0
ORDER BY d DESC;
/*
2018-04-03 11:45:00.000
2018-04-03 11:15:00.000
2018-04-03 10:45:00.000
2018-04-03 10:15:00.000
2018-04-03 09:45:00.000
2018-04-03 09:15:00.000
2018-04-03 08:45:00.000
2018-04-03 08:15:00.000
2018-04-03 07:45:00.000
2018-04-03 07:15:00.000
2018-04-03 06:45:00.000
2018-04-03 06:15:00.000
2018-04-03 05:45:00.000
2018-04-03 05:15:00.000
2018-04-03 04:45:00.000
2018-04-03 04:15:00.000
2018-04-03 03:45:00.000
*/