求高手解答,如何通过sql找出时间连续的记录(非日期连续!)

有一个困扰多日的问题,求高手解答:
记录格式如下:
cardno date time amt oper_id
-------- -------- ------ --------- -------
14010088 20110401 091102 -34.00 jack
14010028 20110401 100101 -100.00 jack
14010002 20110401 100205 -50.00 jack
14010135 20110401 100314 -25.00 jack
14010046 20110401 100546 -10.00 jack
14010970 20110401 100720 -18.00 jack
14010950 20110401 100725 -200.00 tom
14010009 20110401 113048 -30.00 jack
14010077 20110401 113150 -110.00 jack
14020076 20110401 160205 -90.00 simen
14020060 20110401 160508 -3.00 jack
14020111 20110401 160614 -320.00 jack
14010701 20110401 160734 -15.00 jack
14020049 20110402 090500 -10.00 john
14020057 20110402 090614 -600.00 john
14010356 20110402 090805 -50.00 john
......

字段说明:
cardno --卡片号
date --日期
time --时间(hhmmss)
amt --金额
oper_id --操作员

要求:
每天同一个oper_id在间隔3分钟之内的记录视为连续记录,将连续3笔以上的记录找出来,期望得出如下结果:

日期 起始时间 起始卡号 终止时间 终止卡号 记录数 oper_id
-------- -------- -------- -------- -------- ------ -------
20110401 100101 14010028 100720 14010970 5 jack
20110401 160508 14020060 160734 14010701 3 jack
20110402 090500 14020049 090805 14010356 3 john
......

作者: zjnet6   发布时间: 2011-06-16

貌似回答过这样的问题.

作者: qianjin036a   发布时间: 2011-06-16

SQL code
create table tb(cardno varchar(10),[date] varchar(8),[time] varchar(6),amt decimal(8,2),oper_id varchar(10))
insert into tb select '14010088','20110401','091102',-34.00,'jack'
insert into tb select '14010028','20110401','100101',-100.00,'jack'
insert into tb select '14010002','20110401','100205',-50.00,'jack'
insert into tb select '14010135','20110401','100314',-25.00,'jack'
insert into tb select '14010046','20110401','100546',-10.00,'jack'
insert into tb select '14010970','20110401','100720',-18.00,'jack'
insert into tb select '14010950','20110401','100725',-200.00,'tom'
insert into tb select '14010009','20110401','113048',-30.00,'jack'
insert into tb select '14010077','20110401','113150',-110.00,'jack'
insert into tb select '14020076','20110401','160205',-90.00,'simen'
insert into tb select '14020060','20110401','160508',-3.00,'jack'
insert into tb select '14020111','20110401','160614',-320.00,'jack'
insert into tb select '14010701','20110401','160734',-15.00,'jack'
insert into tb select '14020049','20110402','090500',-10.00,'john'
insert into tb select '14020057','20110402','090614',-600.00,'john'
insert into tb select '14010356','20110402','090805',-50.00,'john'
go
;WITH C1 AS(
select row_number()over(partition by oper_id order by [date],[time])rn,CARDNO,[DATE]+' '+STUFF(STUFF(time,3,0,':'),6,0,':')dt,[DATE],[time],amt,oper_id from tb
),c2 as(
select *,1 as flg from c1 a where not exists(select 1 from C1 where oper_id=a.oper_id and DATEDIFF(mi,dt,a.dt)<=3 and DATEDIFF(mi,dt,a.dt)>0)
union all
select a.*,b.flg+1 from C1 a inner join C2 b on a.oper_id=b.oper_id and a.rn=b.rn+1 and DATEDIFF(mi,b.dt,a.dt)<=3
),c3 as(
select * from c2 a where flg>=3 and not exists(select 1 from c2 where oper_id=a.oper_id and rn=a.rn+1 and flg=a.flg+1)
)select b.[date] 日期,b.[time]起始时间,b.cardno 起始卡号,a.[time]终止时间,a.cardno 终止卡号,a.flg 记录数,a.oper_id 
from c3 a inner join C1 b on a.oper_id=b.oper_id and b.rn=a.rn-a.flg+1
/*
日期       起始时间   起始卡号       终止时间   终止卡号       记录数         oper_id
-------- ------ ---------- ------ ---------- ----------- ----------
20110401 100101 14010028   100720 14010970   5           jack
20110401 160508 14020060   160734 14010701   3           jack
20110402 090500 14020049   090805 14010356   3           john

(3 行受影响)
*/
go
drop table tb

作者: qianjin036a   发布时间: 2011-06-16