SQL Server 2000 删除重复记录问题

具体情况为：
表tab1中，只有在3个字段year,month与day完全相同的情况下才能断定为重复数据
需要实现：保留重复记录中的一条,保证数据库内没有两条year,month与day完全相同的数据。
请问该脚本如何实现？

补充一下具体情况,其实有点复杂:
首先我只能根据year, month, day 这3个字段来进行判定重复,不能根据其他任何字段来判断,并且除此之外,还需要判定另外3个字段是否符合各自特定的条件,分别为:
年号(year)在2003与2005之间
作者(author)中含有特定字符
文件号(arcnoindex)字段不为空
ID的确为主键，但我不能根据ID号大小来删除数据，因需符合上述的另外3个条件
补充一句，只能在Sql 2000中进行纯SQL操作，不支持Rowid和row number

以下为我查询时用的脚本,不知是否可以更改为删除脚本
查询条件:arcnoindex不为空，author含有AA或BB字符，year在2003到2005之间，并且year,month与day完全相同的数据。
我只实现了查询，不知如何转换为删除重复数据并保留其中一条。
select a.* from tab1 a ,(select year,word,number from tab1 group by year,word,number having count(*)>1) b
where a.year = b.year and a.word = b.word and a.number = b.number
and year(a.senddate) >=2003 and year(a.senddate)<=2005 and a.arcnoindex is null and a.author in
(select author from tab1 where author='AA or author='BB')

1.存在主键的话这样：（id为主键）
delete from tablename where id not in
(select max(id) from aa group by year,month,day)

2.不存在的话只能这样:
--导入不重复到临时表
select distinct * into #temp from tablename
--清空原表
truncate table tablename
--从临时表倒回原表
insert into tablename select * from #temp

你可以重新建立一个新表，然后将原表中的信息不重复的选出来，复制到新表中，再将原表删除，就ok了。

year, month, day 对应的有其他一个unique字段么？否则的话sql server 2000很难,但是也可以考虑存储过程

查询及删除重复记录的方法大全 1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录 delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1) 3、查找表中多余的重复记录（多个字段） select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录 delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录 select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

count(name) from a1 group by name，还可以复制表结构插入 select distinct name.name in( select name from ( select name;1)) 这个写得有些繁复,value having count(name)>,value having count(name)>,value,count(name) from a1 group by name，还可以设计个视图取消重复的代码段;1)) and value in( select value from ( select name,valuedelete from a1 where a1

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息