求一条去除3分钟内的重复数据的查询。。。急在线等。。。

Question

数据库是SQL server 2000的。有一张就餐表，现在我要除去3分钟内重复刷卡（指工号相同）的记录，只保留一条，3分钟以外的记录我要全部保留并统计他们的总数，id代表记录条数，badge代表员工工号,type是餐次，term是日期，machinename代表刷卡的窗口。
表如下：
id  badge   type          term            machinename
1  IHH111   zhong    2010-07-10 11:30         007
2  IHH111   zhong    2010-07-10 11:30         008
3  IHH111   zhong    2010-07-10 11:31         005
4  IHH222   zhong    2010-07-10 11:31         005
5  IHH222   zhong    2010-07-10 11:36         004
6  IHH222   zhong    2010-07-10 11:30         001
7  IHH333   zhong    2010-07-10 11:40         001
8  IHH444   zhong    2010-07-10 11:35         002
我需要得到的是如下表：
id    badge     type            term              machinename       total
1    IHH111     zhong      2010-07-10 11:30           007             1
4    IHH222     zhong      2010-07-10 11:31           005             2
7    IHH333     zhong      2010-07-10 11:40           001             1
8    IHH444     zhong      2010-07-10 11:35           002             1

Answer 1

结果中的total 没看明白 楼主能进一步解释一下吗

Answer 2

total 就是每个工号（badge）在去掉3分钟内重复条数后的总条数，3分钟内有重复的那就算1条，3分钟外重复的那有几条就算几条，total就是统计条数的列

Answer 3

total 与 007 1 005 2有什么关系 能说清楚吗？

Answer 4

007和005代表在不同卡机上刷卡的machinename是卡机号的字段

Answer 5

看看是不是这样。create table 就餐表(
id int identity(1,1),
badge nvarchar(50),
type nvarchar(20),
term datetime,
machinename nvarchar(30)
)
insert into 就餐表(
badge,type,term,machinename
)
select 'IHH111', 'zhong', '2010-07-10 11:30', '007'
union all
select 'IHH111', 'zhong', '2010-07-10 11:30', '008'
union all
select 'IHH111', 'zhong', '2010-07-10 11:31', '005'
union all
select 'IHH222', 'zhong', '2010-07-10 11:31', '005'
union all
select 'IHH222', 'zhong', '2010-07-10 11:36', '004'
union all
select 'IHH222', 'zhong', '2010-07-10 11:30', '001'
union all 
select 'IHH333', 'zhong', '2010-07-10 11:40', '001'
union all
select 'IHH444', 'zhong', '2010-07-10 11:35', '002'delete from 就餐表 
where  exists( 
select b.id from 就餐表 b 
inner join (select t.badge,t.type,min(t.term) as mterm,
(select top 1 c.id from 就餐表 c where c.badge=t.badge and c.type=t.type and c.term=min(t.term)) as mid
from 就餐表 t group by t.badge,t.type,Convert(nvarchar(10),t.term,120)) a 
on a.badge=b.badge and a.type=b.type and datediff(d,a.mterm,b.term)=0 
where datediff(mi,a.mterm,b.term)<3 and b.id!=a.mid and b.id=就餐表.id  
)
select b.id,b.badge,b.type,b.term,(select count(*)+1 
from 就餐表 d 
where d.badge=b.badge and d.type=b.type and datediff(mi,b.term,d.term)>=3) as total 
from 就餐表 b 
inner join (
select t.badge,t.type,min(t.term) as mterm,
(select top 1 c.id from 就餐表 c 
 where c.badge=t.badge and c.type=t.type and c.term=min(t.term)) as mid  
    from 就餐表 t 
    group by t.badge,t.type,Convert(nvarchar(10),t.term,120)) a 
on a.badge=b.badge and a.type=b.type and datediff(d,a.mterm,b.term)=0 
where  b.id=a.mid

Answer 6

Select * into #tb from (
Select 1 as ID,'IHH111' AS Badge,'zhong' as Type,'2010-07-10 11:30' as Term,'007' as Machinename
 Union All Select 2,'IHH111','zhong','2010-07-10 11:30','008'
 Union All Select 3,'IHH111','zhong','2010-07-10 11:31','005'
 Union All Select 4,'IHH111','zhong','2010-07-10 11:37','005'
 Union All Select 5,'IHH222','zhong','2010-07-10 11:31','005'
 Union All Select 6,'IHH222','zhong','2010-07-10 11:36','004'
 Union All Select 7,'IHH222','zhong','2010-07-10 11:30','001'
 Union All Select 8,'IHH333','zhong','2010-07-10 11:40','001'
 Union All Select 9,'IHH444','zhong','2010-07-10 11:35','002'
) tSelect * from #tb
/*
-- 多加了一条以供测试
ID          Badge  Type  Term             Machinename 
----------- ------ ----- ---------------- ----------- 
1           IHH111 zhong 2010-07-10 11:30 007
2           IHH111 zhong 2010-07-10 11:30 008
3           IHH111 zhong 2010-07-10 11:31 005
4           IHH111 zhong 2010-07-10 11:37 005
5           IHH222 zhong 2010-07-10 11:31 005
6           IHH222 zhong 2010-07-10 11:36 004
7           IHH222 zhong 2010-07-10 11:30 001
8           IHH333 zhong 2010-07-10 11:40 001
9           IHH444 zhong 2010-07-10 11:35 002（所影响的行数为 9 行）
*/Delete from #tb where Not Exists (
Select aa.*,bb.Part from #tb aa inner join (
Select Min(ID) as MinID,a.Badge,a.Type,DateDiff(Minute,b.Term,a.Term)/3 AS Part,Min(a.Term) as MinTerm from #tb a inner join (
Select Badge,Type,Min(Term) as Term from #tb Group by Badge,Type
) b on a.Badge=b.Badge and a.Type=b.Type
 Group by a.Badge,a.Type,DateDiff(Minute,b.Term,a.Term)/3
) bb on aa.Badge=bb.Badge and aa.Type=bb.Type and aa.ID=bb.MinID and aa.Term Between bb.MinTerm and DateAdd(Minute,3,bb.MinTerm)
 and aa.ID=#tb.ID
)Select * from #tb
/*
ID          Badge  Type  Term             Machinename 
----------- ------ ----- ---------------- ----------- 
1           IHH111 zhong 2010-07-10 11:30 007
4           IHH111 zhong 2010-07-10 11:37 005
5           IHH222 zhong 2010-07-10 11:31 005
6           IHH222 zhong 2010-07-10 11:36 004
8           IHH333 zhong 2010-07-10 11:40 001
9           IHH444 zhong 2010-07-10 11:35 002（所影响的行数为 6 行）
*/

Answer 7

ID          Badge  Type  Term             Machinename 
----------- ------ ----- ---------------- ----------- 
4           IHH111 zhong 2010-07-10 11:37 005
这条是为了检验是否以三分钟为区间新增进去的,和源数据有点不一样了,呵呵

Answer 8

select  m.*,
       (select  count(badge) from 
  (select  * from eat_record_all t where  
                        not exists 
                         (select * from eat_record_all  where badge=t.badge and abs( datediff(n,t.term,term))>=1 and abs(datediff(n,t.term,term))<=3) 
  )n
  where
  n.badge=m.badge 
  )total
from (
       select  * from eat_record_all v
           where
       not exists 
        (select * from eat_record_all  where badge=v.badge and abs( datediff(n,v.term,term))>=1 and abs(datediff(n,v.term,term))<=3)
)m 
order by m.badge, m.term
这是我自己写的，但它只能去除1分钟到3分钟重复刷卡的记录，在同1分钟内的刷卡记录不能删除，如果要删除同一分钟内的记录就要再加个distinct，但这样一来只能查询字段Badge，Type，Term，而且速度也会变的很慢，求高手帮忙，我的表数据大概50万，我要查出来的是每天的记录大概1万条。

Answer 9

原始数据不能删除啊，数据库不是我的，我只是在别人的基础上做一个查询系统，我只有查询权限，没有删除权限，你写的我还测试，等下我把那些数据拷贝的我的数据库来测试下，但是里面的select语句我测试了下速度有点慢

Answer 10

有没有具体语句啊，我接触sql没几天，很多都不懂，希望指点下，谢谢

Answer 11

晕哦，那你不要用那条delete不就行了？
只用后面的select。
select b.id,b.badge,b.type,b.term,(select count(*)+1 
from 就餐表 d 
where d.badge=b.badge and d.type=b.type and datediff(mi,b.term,d.term)>=3) as total 
from 就餐表 b 
inner join (
    select t.badge,t.type,min(t.term) as mterm,
        (select top 1 c.id from 就餐表 c 
         where c.badge=t.badge and c.type=t.type and c.term=min(t.term)) as mid  
    from 就餐表 t 
    group by t.badge,t.type,Convert(nvarchar(10),t.term,120)) a 
on a.badge=b.badge and a.type=b.type and datediff(d,a.mterm,b.term)=0 
where  b.id=a.mid

Answer 12

我测试了，速度慢，而且total也不正确啊

Answer 13

我测试了，速度慢，而且total也不正确啊
速度我不清楚，不过那个total没有错吧，
比如什么数据
我这里查询出来的total有错？

Answer 14

直接用那个select是有错，要跟delete一起用才得，我看看。

Answer 15

--这个查询应该可以了
select (select top 1 c.id from 就餐表 c 
 where c.badge=t.badge and c.type=t.type and c.term=min(t.term)) as id,t.badge,t.type,min(t.term) as mterm,
   (select count(*)+1 
from 就餐表 d 
where d.badge=t.badge and d.type=t.type and datediff(mi,min(t.term),d.term)>=3) as total 
    from 就餐表 t 
    group by t.badge,t.type,Convert(nvarchar(10),t.term,120)--速度慢的话你创建个索引看看
create index cindex on 就餐表(
badge asc,
type asc,
term asc
)

Answer 16

我刚才看了下，我上面的例子中我少了一条，我要得结果是
id badge type term  machinename total
1 IHH111 zhong 2010-07-10 11:30 007  1
5 IHH222 zhong 2010-07-10 11:36 004  2
6 IHH222 zhong 2010-07-10 11:30 001  2
7 IHH333 zhong 2010-07-10 11:40 001  1
8 IHH444 zhong 2010-07-10 11:35 002  1你的查询已经满足我原先的要求了，就是速度慢了点，50数据的表中找4000条数据用了14秒。

Answer 17

Drop Table #tb
Select * into #tb from (
Select 1 as ID,'IHH111' AS Badge,'zhong' as Type,'2010-07-10 11:30' as Term,'007' as Machinename
 Union All Select 2,'IHH111','zhong','2010-07-10 11:30','008'
 Union All Select 3,'IHH111','zhong','2010-07-10 11:31','005'
 Union All Select 4,'IHH222','zhong','2010-07-10 11:31','005'
 Union All Select 5,'IHH222','zhong','2010-07-10 11:36','004'
 Union All Select 6,'IHH222','zhong','2010-07-10 11:30','001'
 Union All Select 7,'IHH333','zhong','2010-07-10 11:40','001'
 Union All Select 8,'IHH444','zhong','2010-07-10 11:35','002'
) tSelect * from #tb
/*
ID          Badge  Type  Term             Machinename 
----------- ------ ----- ---------------- ----------- 
1           IHH111 zhong 2010-07-10 11:30 007
2           IHH111 zhong 2010-07-10 11:30 008
3           IHH111 zhong 2010-07-10 11:31 005
4           IHH222 zhong 2010-07-10 11:31 005
5           IHH222 zhong 2010-07-10 11:36 004
6           IHH222 zhong 2010-07-10 11:30 001
7           IHH333 zhong 2010-07-10 11:40 001
8           IHH444 zhong 2010-07-10 11:35 002（所影响的行数为 8 行）
*/
Select aa.*,bb.Total,bb.Part from #tb aa inner join (
Select Min(ID) as MinID,a.Badge,a.Type,DateDiff(Minute,b.Term,a.Term)/3 AS Part,Min(a.Term) as MinTerm
,Count(ID) as Total
 from #tb a inner join (
Select Badge,Type,Min(Term) as Term from #tb Group by Badge,Type
) b on a.Badge=b.Badge and a.Type=b.Type
 Group by a.Badge,a.Type,DateDiff(Minute,b.Term,a.Term)/3
) bb on aa.Badge=bb.Badge and aa.Type=bb.Type and aa.ID=bb.MinID and aa.Term Between bb.MinTerm and DateAdd(Minute,3,bb.MinTerm)/*
ID          Badge  Type  Term             Machinename Total       Part        
----------- ------ ----- ---------------- ----------- ----------- ----------- 
1           IHH111 zhong 2010-07-10 11:30 007         3           0
4           IHH222 zhong 2010-07-10 11:31 005         2           0
5           IHH222 zhong 2010-07-10 11:36 004         1           2
7           IHH333 zhong 2010-07-10 11:40 001         1           0
8           IHH444 zhong 2010-07-10 11:35 002         1           0（所影响的行数为 5 行）
*/