重復記錄：在某一段時間內多次刷卡只算一次
重復記錄：在某一段時間內多次刷卡只算一次
如以下刷卡記錄：
Cno cDateTime
171 2006/09/14   14:06:00
171 2006/09/14   14:30:00
171 2006/09/14   14:50:00
1171 2006/09/14   14:05:00
1171 2006/09/14   15:00:00
1171 2006/09/14   16:06:00

如果在前後兩次刷卡時間沒有超過30分鐘則只算一條，結果如下：
Cno cDateTime
171 2006/09/14   14:06:00
171 2006/09/14   14:50:00
1171 2006/09/14   14:05:00
1171 2006/09/14   15:00:00
1171 2006/09/14   16:06:00



------解决方案--------------------
try


Delete A From 表 A
Where Exists(Select Cno From 表 Where Cno = A.Cno And DateDiff(mi, cDateTime, A.cDateTime) <= 30)
------解决方案--------------------
1楼的不行:

171 2006/09/14 14:06:00
171 2006/09/14 14:30:00
171 2006/09/14 14:50:00


这三条记录相差都不超过30 分钟, 归一楼的方法只会出1条, 但楼主要求的是出2条
------解决方案--------------------
declare @t table(Cno int,cDateTime datetime)
insert @t
select 171, '2006/09/14 14:06:00 ' union all
select 171, '2006/09/14 14:30:00 ' union all
select 171, '2006/09/14 14:50:00 ' union all
select 1171, '2006/09/14 14:05:00 ' union all
select 1171, '2006/09/14 15:00:00 ' union all
select 1171, '2006/09/14 16:06:00 '
select * from @t

select * from @t as a where exists
(select 1 from @t where Cno = a.Cno and datediff(minute,cDateTime,a.cDateTime) > 30)
or
not exists(select 1 from @t where Cno = a.Cno and cDateTime < a.cDateTime)


/*結果
Cno cDateTime
-------------------------------
171 2006/09/14 14:06:00
171 2006/09/14 14:50:00
1171 2006/09/14 14:05:00
1171 2006/09/14 15:00:00
1171 2006/09/14 16:06:00
*/


------解决方案--------------------
--方法二：update临时表
create table tb(Cno int,cDateTime datetime)
insert tb
select 171 , '2006/09/14 14:06:00 ' union all
select 171 , '2006/09/14 14:30:00 ' union all
select 171 , '2006/09/14 14:40:00 ' union all
select 171 , '2006/09/14 14:50:00 ' union all
select 171 , '2006/09/14 15:06:00 ' union all
select 171 , '2006/09/14 14:55:00 ' union all
select 1171 , '2006/09/14 14:05:00 ' union all
select 1171 , '2006/09/14 15:00:00 ' union all
select 1171 , '2006/09/14 16:06:00 '

go
select *,1 as flag into #tb from tb order by cno,cDateTime
declare @cno int,@cDateTime datetime,@flag int

update #tb
set @flag = case when @cno = cno and datediff(mi,@cDateTime,cDateTime) < 30 then 0 else 1 end
,@cno = cno,@cDateTime = case when @flag = 0 then @cDateTime else cDateTime end,flag = @flag

select * from #tb where flag = 1
go
drop table tb,#tb
/*
Cno cDateTime flag
----------- ------------------------------------------------------ -----------
171 2006-09-14 14:06:00.000 1
171 2006-09-14 14:40:00.000 1
1171 2006-09-14 14:05:00.000 1
1171 2006-09-14 15:00:00.000 1
1171 2006-09-14 16:06:00.000 1

(5 row(s) affected)
*/

------解决方案--------------------