删除表中字段重重的记录
有一表A,字段ID多有重复,我用下语句不符合我的要求
select distinct * into tablename1
from tablename2
这样别的字段要是还有重复的话ID字段还是会有重复的,
我又用以下语句
select distinct ID into tablename1
from tablename2
这样新建的表又只有ID一个字段,请问一下
如何才能使表中ID唯一,又能把tablename2中所有字段的值COPY过来,同时删除ID重复的字段?
[解决办法]
相同的id保留第一条用什么字段来判定，例如取时间最大的那条：
select * from test a where not exists(select 1 from test where id=a.id and [date]> a.[date])
[解决办法]
在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?谢谢!
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)
[解决办法]
先给这个表加一个自增长列Rid
然后
select y.*
(
select ID,Rid=Max(Rid) From tablename1
) as x join tablename1 y on x.ID=y.ID and x.Rid=y.Rid
不知道和不符合你的要求
[解决办法]
錯了，是針對這條回復的。
littlepig991(朱朱) ( ) 信誉：100 Blog 2007-3-13 10:28:08 得分: 0
表中没有关键字,重复的ID随便保留一条,不过最好是保留第一条
-------------------
Create Table tablename2
(IDInt,
NameVarchar(10))
Insert tablename2
Select 1, 'AA '
Union All Select 1, 'BB '
Union All Select 2, 'CC '
Union All Select 2, 'DD '
Union All Select 2, 'EE '
Union All Select 5, 'FF '
GO
Select TESTID = Identity(Int, 1, 1), * Into #T From tablename2
Select ID, Name Into tablename1 From #T Where TESTID In (Select Min(TESTID) From #T Group By ID)
Select * From tablename1
Drop Table #T
GO
Drop Table tablename2, tablename1
--result
/*
IDName
1AA
2CC
5FF
*/
