Question

sqlserver 时间递增循环 dateadd 怎么样循环呢，由于1分钟有多条数据，但是我只想取1分钟的第一条数据

如：
DIA 2011-9-27 08:10:30 88
DIA 2011-9-27 08:10:20 23
DIA 2011-9-27 08:10:10 44
SYS 2011-9-27 23:54:48 98
DIA 2011-9-27 23:54:48 31
SYS 2011-9-27 23:54:52 98
DIA 2011-9-27 23:54:52 31
SYS 2011-9-27 23:55:51 98

我想要 2011-9-27 08:10 的取一条
2011-9-27 23:55的取一条

下面的存储过程却没有成功，真是纳闷得很
DDECLARE @TI datetime,@t2 datetime,@n int
SET @TI='2011/9/27 7:00'
set @n = 1
set @t2 = dateadd(minute,1,@TI)
WHILE (@n < 240 )--循环4个小时（即240分钟）
BEGIN
insert into OPS_Monitor_Item_Result_temp2

SELECT top 1 *
FROM OPS_Monitor_Item_Result
WHERE equipment_field_name ='SYS'
and monitoring_time >= @TI
and monitoring_time < @t2
order by monitoring_time

SET @TI=dateadd(minute,1,@TI)
set @t2 = dateadd(minute,1,@TI)
set @n = @n +1
END

麻烦各位

Answer 1

直接一个sql 就可以做的事情：

select equipment_field_name, monitoring_time, 第三列
from (select equipment_field_name,
monitoring_time,
第三列,
row_number() over(partition by substring(convert(varchar, getdate(), 120), 1, 16) order by monitoring_time) rn
from ops_monitor_item_result) a
where a.rn = 1