有一表A,字段ID多有重复,我用下语句不符合我的要求
select distinct * into tablename1
from tablename2
这样别的字段要是还有重复的话ID字段还是会有重复的,
我又用以下语句
select distinct ID into tablename1
from tablename2
这样新建的表又只有ID一个字段,请问一下
如何才能使表中ID唯一,又能把tablename2中所有字段的值COPY过来,同时删除ID重复的字段?
select distinct * into tablename1
from tablename2
这样别的字段要是还有重复的话ID字段还是会有重复的,
我又用以下语句
select distinct ID into tablename1
from tablename2
这样新建的表又只有ID一个字段,请问一下
如何才能使表中ID唯一,又能把tablename2中所有字段的值COPY过来,同时删除ID重复的字段?
select * from test a where not exists(select 1 from test where id=a.id and [date]>a.[date])
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)
(select id , min(id1) as id1 from test group by id) b --也可以用max
where a.id = b.id and a.id1 = b.id1
然后
select y.*
(
select ID,Rid=Max(Rid) From tablename1
) as x join tablename1 y on x.ID=y.ID and x.Rid=y.Rid不知道和不符合你的要求
重新生成ID的话那些重复的记录也重新生成ID了,现在是要把重复的记录删除
---------------
Create Table tablename2
(ID Int,
Name Varchar(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 tablename2Select ID, Name Into tablename1 From #T Where TESTID In (Select Min(TESTID) From #T Group By ID)Select * From tablename1Drop Table #T
GO
Drop Table tablename2, tablename1
--result
/*
ID Name
1 AA
2 CC
5 FF
*/
表中没有关键字,重复的ID随便保留一条,不过最好是保留第一条
-------------------Create Table tablename2
(ID Int,
Name Varchar(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 tablename2Select ID, Name Into tablename1 From #T Where TESTID In (Select Min(TESTID) From #T Group By ID)Select * From tablename1Drop Table #T
GO
Drop Table tablename2, tablename1
--result
/*
ID Name
1 AA
2 CC
5 FF
*/