求高手解答，如何通过sql找出时间连续的记录（非日期连续！）

Question

有一个困扰多日的问题，求高手解答：
记录格式如下：
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
......

Answer 1

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

Answer 2

use tempdb;
go
declare @t table(
cardno char(8)
,[date] char(8)
,[time] char(6)
,amt decimal(9,2)
,oper_id varchar(8)
);
insert into @t select '14010088','20110401','091102',-34.00,'jack'
insert into @t select '14010028','20110401','100101',-100.00,'jack'
insert into @t select '14010002','20110401','100205',-50.00,'jack'
insert into @t select '14010135','20110401','100314',-25.00,'jack'
insert into @t select '14010046','20110401','100546',-10.00,'jack'
insert into @t select '14010970','20110401','100720',-18.00,'jack'
insert into @t select '14010950','20110401','100725',-200.00,'tom'
insert into @t select '14010009','20110401','113048',-30.00,'jack'
insert into @t select '14010077','20110401','113150',-110.00,'jack'
insert into @t select '14020076','20110401','160205',-90.00,'simen'
insert into @t select '14020060','20110401','160508',-3.00,'jack'
insert into @t select '14020111','20110401','160614',-320.00,'jack'
insert into @t select '14010701','20110401','160734',-15.00,'jack'
insert into @t select '14020049','20110402','090500',-10.00,'john'
insert into @t select '14020057','20110402','090614',-600.00,'john'
insert into @t select '14010356','20110402','090805',-50.00,'john'
;WITH 
--增加排序
Result AS
(
SELECT ROW_NUMBER() over(order by date,time) as IndexNo,* FROM @t
),
--标记每条记录前一条和后一条是否在3分钟之内
Results as
(
SELECT * from Result r1
outer apply
(
SELECT CASE WHEN time<=r1.time+300 then 1 else null end as tag1
FROM Result 
WHERE oper_id=r1.oper_id and date=r1.date  and IndexNo=r1.IndexNo+1
) r2
outer apply
(
SELECT CASE WHEN time>=r1.time-300 then 1 else null end as tag2
FROM Result 
WHERE oper_id=r1.oper_id and date=r1.date  and IndexNo=r1.IndexNo-1
) r3
),
--连续记录第一条
Mins AS
(
SELECT IndexNo,cardno,date,time,oper_id
FROM Results
WHERE tag2 IS NULL and tag1=1
),
--连续记录最后条
Maxs AS
(
SELECT IndexNo,cardno,date,time,oper_id
FROM Results
WHERE tag1 IS NULL and tag2=1
),
--第一条跟最后一条记录编号关联
Finals AS
(
SELECT *
FROM Mins m1
CROSS APPLY
(
SELECT MIN(indexno) as IndexNo2
from Maxs 
where oper_id=m1.oper_id and date=m1.date and IndexNo>m1.IndexNo
) m2
)
SELECT m1.date as [日期], m1.time as [起始时间],m1.cardno as [起始卡号], m2.time as [终止时间], m2.cardno as [终止卡号]
  ,m2.IndexNo-m1.IndexNo+1 as [记录数], m1.oper_id
FROM Finals m1 JOIN 
     Maxs m2 ON m1.IndexNo2=m2.IndexNo
WHERE m2.IndexNo-m1.IndexNo>=2/*结果集日期     起始时间   起始卡号     终止时间   终止卡号 记录数 oper_id
-------- --------   --------     ------     -------- ------ --------
20110401 100101     14010028  100720     14010970 5      jack
20110401 160508     14020060     160734     14010701 3      jack
20110402 090500     14020049     090805     14010356 3      john
*/

Answer 3

三楼朋友的思路比较清晰，很有帮助。先采用加序号再比对确实是个好思路，可是手头的数据库环境是Sybase ASE 12.5，不支持rownumber()、outer apply、outer apply等语法，如何修改为能在sybase上运行的sql？望高手再指点一下。

Answer 4

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(
--用stuff函数将时间格式化后与日期连接,成为一个完整的时间日期型值,同时,以oper_id分组,时间排序,设置分组序号 rn
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(
--先找出某个记录前3分钟内该 id 没有记录的记录,作为递归切入点,flg是一个标志,表明它是递归的第一条
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
--递归,找出从递归切入点处开始的,时间与递归循环内前一条记录的id相同,且时间小于等于3分钟的记录,并以找不到3分钟内的记录为递归边界条件
--同时,每一次递归,循环数标志加1
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(
--找出循环数>=3的最后一次递归的记录
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

Answer 5

其实,这组语句都可以分开来重写,比如,用row_number的可以用 identity函数单独处理后写入临时表,相当于上面的c1
递归可以用循环程序来完成,递归就是循环.
明白了算法,剩下的只有语法了.

Answer 6

谢谢晴天的指点，有些明白了。但是使用递归的写法，在数据量较大时sqlserver报错如下：
消息 530，级别 16，状态 1，第 1 行
语句被终止。完成执行语句前已用完最大递归 100。
实际记录条数约10万条，继续盼望晴天的指点！