MYSQL查询重复记录的方法

MYSQL查询重复记录的方法很多,下面就为您介绍几种最常用的MYSQL查询重复记录的方法,希望对您学习MYSQL查询重复记录方面能有所帮助。

1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断

  1. select * from people
  2. where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录

  1. delete from people
  2. where peopleId in (select peopleId from people group by peopleId   having count(peopleId) > 1)
  3. and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3、查找表中多余的重复记录(多个字段)

  1. select * from vitae a
  2. where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录

  1. delete from vitae a
  2. where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
  3. and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录

  1. select * from vitae a
  2. where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
  3. and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

thedarkside

这是一个最好的时代,这是一个最坏的时代。

stay connected

228,480

Fans

21,563

Followers

20,563

Followers

8,125

Subscribers

2,253

Subscribers

10,563

Followers

最新文章

entertainment
Buckingham Palace soil used in Tate exhibit
Oct 13, 2016
Sports
Legendary coach Steve Spurrier was truly
Oct 13, 2016
Technology
Acer reveals all-in-one Windows 10 PC
Oct 13, 2016
entertainment
Revival allows Selena Gomez to shed
Oct 13, 2016
entertainment
Buckingham Palace soil used in Tate exhibit
Oct 13, 2016
Sports
Legendary coach Steve Spurrier was truly
Oct 13, 2016
Technology
Acer reveals all-in-one Windows 10 PC
Oct 13, 2016
entertainment
Revival allows Selena Gomez to shed
Oct 13, 2016