建表语句
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
insert into Persons values(1,'a','aa','aaa','aaaa');
insert into Persons values(1,'a','aa','aaa','aaaa');
insert into Persons values(1,'a','aa','aaa','aaaa');
insert into Persons values(1,'a','aa','aaa','aaaa');
insert into Persons values(2,'a','aa','aaa','aaaa');
insert into Persons values(2,'a','aa','aaa','aaaa');
insert into Persons values(3,'a','aa','aaa','aaaa');
insert into Persons values(3,'a','aa','aaa','aaaa');
insert into Persons values(4,'a','aa','aaa','aaaa');
insert into Persons values(5,'a','aa','aaa','aaaa');
select * from Persons order by PersonID;
PERSONID LASTNAME FIRSTNAME ADDRESS CITY
1 1 a aa aaa aaaa
2 1 a aa aaa aaaa
3 1 a aa aaa aaaa
4 1 a aa aaa aaaa
5 2 a aa aaa aaaa
6 2 a aa aaa aaaa
7 3 a aa aaa aaaa
8 3 a aa aaa aaaa
9 4 a aa aaa aaaa
10 5 a aa aaa aaaa
1、查找表中多余的重复记录，重复记录是根据单个字段（PersonID）来判断。
select * from Persons where PersonID in ( select PersonID from Persons group by PersonID having(count(PersonID))>1);
order by PersonID;
PERSONID LASTNAME FIRSTNAME ADDRESS CITY
1 a aa aaa aaaa
1 a aa aaa aaaa
1 a aa aaa aaaa
1 a aa aaa aaaa
2 a aa aaa aaaa
2 a aa aaa aaaa
3 a aa aaa aaaa
3 a aa aaa aaaa
2、删除表中多余的重复记录，重复记录是根据单个字段（PersonID）来判断，只留有rowid最小的记录。
select * from Persons
where PersonID in (select PersonID from Persons group by PersonID having count(PersonID) > 1)
and rowid not in (select min(rowid) from Persons group by PersonID having count(PersonID )>1)
order by PersonID;
delete from Persons
where PersonID in (select PersonID from Persons group by PersonID having count(PersonID) > 1)
and rowid not in (select min(rowid) from Persons group by PersonID having count(PersonID )>1)
PERSONID LASTNAME FIRSTNAME ADDRESS CITY
1 a aa aaa aaaa
1 a aa aaa aaaa
1 a aa aaa aaaa
2 a aa aaa aaaa
3 a aa aaa aaaa
3、查找表中多余的重复记录（多个字段）
select * from Persons a
where (a.PersonID,a.City) in (select PersonID,City from Persons group by PersonID,City having count(*) > 1)
order by PersonID;
PERSONID LASTNAME FIRSTNAME ADDRESS CITY
1 a aa aaa aaaa
1 a aa aaa aaaa
1 a aa aaa aaaa
1 a aa aaa aaaa
2 a aa aaa aaaa
2 a aa aaa aaaa
3 a aa aaa aaaa
3 a aa aaa aaaa
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
select * from Persons a
where (a.PersonID,a.City) in (select PersonID,City from Persons group by PersonID,City having count(*) > 1)
and rowid not in (select min(rowid) from Persons group by PersonID,City having count(*)>1)
order by PersonID;
delete from Persons a
where (a.PersonID,a.City) in (select PersonID,City from Persons group by PersonID,City having count(*) > 1)
and rowid not in (select min(rowid) from Persons group by PersonID,City having count(*)>1)
PERSONID LASTNAME FIRSTNAME ADDRESS CITY
1 a aa aaa aaaa
1 a aa aaa aaaa
1 a aa aaa aaaa
2 a aa aaa aaaa
3 a aa aaa aaaa
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from Persons a
where (a.PersonID,a.City) in (select PersonID,City from Persons group by PersonID,City having count(*) > 1)
and rowid not in (select min(rowid) from Persons group by PersonID,City having count(*)>1)
and rowid not in (select min(rowid) from Persons group by PersonID,seq having count(*)>1)
order by PersonID;
PERSONID LASTNAME FIRSTNAME ADDRESS CITY
1 a aa aaa aaaa
1 a aa aaa aaaa
1 a aa aaa aaaa
2 a aa aaa aaaa
3 a aa aaa aaaa