22,209
社区成员
发帖
与我相关
我的任务
分享
--测试数据为18号到20号全天
;WITH t AS (
SELECT * FROM master.dbo.spt_values WHERE type='P'
)
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) ID,DATEADD(n,t.number*10+CHECKSUM(NEWID())%5,'2016-05-18 00:05:00') RecordTime--,CHECKSUM( NEWID())%5
INTO #t
FROM t
WHERE DATEADD(n,t.number*10,'2016-05-18')<'2016-05-21';
--查询
DECLARE @beginTime DATETIME='2016-05-18 08:05:00';
DECLARE @endTime DATETIME='2016-05-20 20:50:00';
;WITH t AS (
SELECT * FROM master.dbo.spt_values WHERE type='P'
)
,tt AS (
SELECT DATEADD(n,t.number*30,@beginTime) StandardTime ,number
FROM t
WHERE DATEADD(n,t.number*30,@beginTime)<DATEADD(n,30,@endTime)
)
SELECT tt.StandardTime,MAX(#t.RecordTime) RecordTime
FROM tt JOIN #t ON #t.RecordTime>DATEADD(n,-30,tt.StandardTime) AND #t.RecordTime<=tt.StandardTime
WHERE RecordTime>DATEADD(n,-30,@beginTime) AND RecordTime<=@endTime
GROUP BY tt.StandardTime
ORDER BY 1;
declare @beg_date datetime
declare @end_date datetime
set @beg_date = '2016-05-18 08:05:00' --起始时间点
set @end_date = '2016-05-20 08:50:00' --截止时间点
select DATEADD(MI,30*number,@beg_date) as date_value
from master..spt_values t
where type = 'P'
and number between 0 and DATEDIFF(MI,@beg_date,@end_date)/30
;WITH Dt
AS
(
SELECT CAST('2016-05-18 08:05' AS DATETIME) AS Col
UNION ALL
SELECT CASE WHEN Col+'00:30'>='2016-05-20 20:50' THEN '2016-05-20 20:50' ELSE Col+'00:30' END FROM Dt WHERE Col<'2016-05-20 20:50'
)
SELECT * FROM Dt OPTION(MAXRECURSION 0)