比如一个表 有两个字段
ID datetime
1 2010-01-2 19:30:00
2 2010-01-2 18:30:00
3 2010-01-2 18:10:00
4 2010-01-2 20:10:00
5 2010-01-2 19:20:00数据如上,我现在想用SQL语句,对所有记录按照时间排序,计算出两个最近时间间隔大于半小时的次数比如上面数据
排序得出结果为
3 2010-01-2 18:10:00
2 2010-01-2 18:30:00
5 2010-01-2 19:20:00
1 2010-01-2 19:30:00
4 2010-01-2 20:10:00
时间升序两条相邻记录之间相隔超过 半小时的 共有2条
即 (2 2010-01-2 18:30:00) 与 (5 2010-01-2 19:20:00)和
(1 2010-01-2 19:30:00)与(4 2010-01-2 20:10:00) 相隔半小时上
ID datetime
1 2010-01-2 19:30:00
2 2010-01-2 18:30:00
3 2010-01-2 18:10:00
4 2010-01-2 20:10:00
5 2010-01-2 19:20:00数据如上,我现在想用SQL语句,对所有记录按照时间排序,计算出两个最近时间间隔大于半小时的次数比如上面数据
排序得出结果为
3 2010-01-2 18:10:00
2 2010-01-2 18:30:00
5 2010-01-2 19:20:00
1 2010-01-2 19:30:00
4 2010-01-2 20:10:00
时间升序两条相邻记录之间相隔超过 半小时的 共有2条
即 (2 2010-01-2 18:30:00) 与 (5 2010-01-2 19:20:00)和
(1 2010-01-2 19:30:00)与(4 2010-01-2 20:10:00) 相隔半小时上
declare @tb table (id int identity,dt datetime);
insert into @tb (dt)
select '2010-01-2 19:30:00' union all
select '2010-01-2 18:30:00' union all
select '2010-01-2 18:10:00' union all
select '2010-01-2 20:10:00' union all
select '2010-01-2 19:20:00';
select COUNT(*) from @tb t
where DATEDIFF(MI,dt,(select min(dt) from @tb where dt>t.dt))>30;
(select id,dt1=datetime,dt2=(select min(datetime) from t1 where datetime>t.datetime)
from t1 t) t2
where datediff(n, dt1, dt2)>=30
SELECT M.* FROM TAB M
LEFT JOIN TAB N
ON CONVERT(INT,DATEDIFF('mm',M.datetime,N.datetime))>30
insert into tbk
select '2010-01-2 19:30:00' union all
select '2010-01-2 18:30:00' union all
select '2010-01-2 18:10:00' union all
select '2010-01-2 20:10:00' union all
select '2010-01-2 19:20:00';SELECT M.dt,N.dt FROM
(SELECT * ,ROW_NUMBER()OVER(ORDER BY DT) AS MROW FROM(
SELECT * ,ROW_NUMBER()OVER(ORDER BY DT) AS 'ROENUMBER'
FROM TBK
)M
WHERE M.ROENUMBER % 2=1)M
LEFT JOIN
(SELECT * ,ROW_NUMBER()OVER(ORDER BY DT) AS NROW FROM(
SELECT * ,ROW_NUMBER()OVER(ORDER BY DT) AS 'ROENUMBER'
FROM TBK
)M
WHERE M.ROENUMBER % 2=0)N
ON (DATEDIFF(mi,M.dt,N.dt)>=30 OR DATEDIFF(mi,N.dt,M.dt)>=30)
AND (M.MROW-N.NROW)=1 OR (N.NROW-M.MROW)=1
AND M.MROW=N.NROW
WHERE LEN(N.dt)>0dt dt
----------------------- -----------------------
2010-01-02 19:20:00.000 2010-01-02 18:30:00.000
2010-01-02 20:10:00.000 2010-01-02 19:30:00.000(2 row(s) affected)
Insert Into @tb(DTime)
Select '2010-01-2 19:30:00' Union All
Select '2010-01-2 18:30:00' Union All
Select '2010-01-2 18:10:00' Union All
Select '2010-01-2 20:10:00' Union All
Select '2010-01-2 19:20:00' Select *,0 Flag Into #Tmp From @tb Order By DTimeDeclare @ID Int,@ID1 Int,@DTime VarChar(24),@DTime1 VarChar(24)Select Top 1 @ID1 = ID,@DTime1 = DTime From #TmpSet RowCount 2Declare Cur_A Cursor For Select ID,DTime From #Tmp
Open Cur_A
Fetch Next From Cur_A Into @ID,@DTime
While @@Fetch_Status = 0
Begin
If DateDiff(Minute,@DTime1,@DTime) >= 30
Insert Into #Tmp(DTime,Flag) Select Convert(VarChar(10),@ID1) + ' ' + @DTime1 + '/' + Convert(VarChar(10),@ID) + ' ' + @DTime,1 Select @ID1 = @ID,@DTime1 = @DTime
Fetch Next From Cur_A Into @ID,@DTime
End
Close Cur_A
DealLocate Cur_ASet RowCount 0Select DTime 显示结果 From #Tmp Where Flag = 1
Drop Table #Tmp