SQL server如何在一定时间范围内, 按整点统计数据?

[粹白]

新手一枚, 2天了还是没找到办法, 希望各位前辈能来赐教




SQL server2008 表里是近2年, 每天定时记录的数据.
现在需要取出在指定范围内, 每天整点的数据; 如果没有整点数据, 就按照最接近整点的时间点取.

咋办捏咋办捏, 望指点!!

[听雨停了]

IF OBJECT_ID('tempdb..#tab') IS NOT NULL
	DROP TABLE #tab
CREATE TABLE #tab(
	dh_datetime DATETIME 	
)	
DECLARE @i INT,
		@a INT
SET @a=1		 
SET @i=1000
WHILE @a<@i
BEGIN
	INSERT INTO #tab
	SELECT dateadd(minute,@a,GETDATE())
	SET @a=@a+1
END
SELECT * FROM #tab
--测试数据结束


DECLARE @begindt DATETIME,	--指定范围开始时间
		@endtime DATETIME	--指定范围结束时间
SET @begindt='2018-02-05 13:42:05.320'
SET @endtime='2018-02-06 06:20:05.433'
;WITH cte AS (
	SELECT *,
		   ROW_NUMBER() OVER(
			   PARTITION BY CAST(dh_datetime AS DATE),
			   DATEPART(hour, dh_datetime) ORDER BY dh_datetime
		   ) AS rn
	FROM   #tab
	WHERE dh_datetime BETWEEN @begindt AND @endtime
)
SELECT dh_datetime FROM cte 
WHERE rn=1

[t101lian]

   SELECT * FROM (
   SELECT ROW_NUMBER() OVER(PARTITION BY CONVERT(varchar(13),DH_DataTime,120) ORDER BY DH_DataTime) AS num  ,
 * FROM 表名
 ) a WHERE num=1

[t101lian]

   SELECT   *
   FROM     ( SELECT    ROW_NUMBER() OVER ( PARTITION BY CONVERT(VARCHAR(13), DH_DataTime, 120)
    ORDER BY DH_DataTime ) AS num , * FROM  表名  ) a
   WHERE    num = 1;

[粹白]

引用 4 楼 t101lian 的回复:

   SELECT   *
   FROM     ( SELECT    ROW_NUMBER() OVER ( PARTITION BY CONVERT(VARCHAR(13), DH_DataTime, 120)
    ORDER BY DH_DataTime ) AS num , * FROM  表名  ) a
   WHERE    num = 1;

弄好了, 谢谢!

[粹白]

数据的时间间隔没有固定规律,有些是整点过后30分钟才有数据