重復記錄：在某一段時間內多次刷卡只算一次
重復記錄：在某一段時間內多次刷卡只算一次
如以下刷卡記錄：
CnocDateTime
1712006/09/14 14:06:00
1712006/09/14 14:30:00
1712006/09/14 14:50:00
11712006/09/14 14:05:00
11712006/09/14 15:00:00
11712006/09/14 16:06:00
如果在前後兩次刷卡時間沒有超過30分鐘則只算一條，結果如下：
CnocDateTime
1712006/09/14 14:06:00
1712006/09/14 14:50:00
11712006/09/14 14:05:00
11712006/09/14 15:00:00
11712006/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楼的不行:
1712006/09/14 14:06:00
1712006/09/14 14:30:00
1712006/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)
/*結果
CnocDateTime
-------------------------------
1712006/09/14 14:06:00
1712006/09/14 14:50:00
11712006/09/14 14:05:00
11712006/09/14 15:00:00
11712006/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)
*/
------解决方案--------------------
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:31: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 a.cno,min(a.cDateTime) from #tb a
left join (select cno,min(cdatetime)[cdatetime] from #tb group by cno) b
on b.cno=a.cno
group by a.cno,datediff(n,b.cdatetime,a.cdatetime)/30
