mysql中distinct和group by過濾重複資料刪除行

下面先來看看例子：

代碼如下	複製代碼
table id name 1 a 2 b 3 c 4 c 5 b

庫結構大概這樣，這隻是一個簡單的例子，實際情況會複雜得多。

比如我想用一條語句查詢得到name不重複的所有資料，那就必須使用distinct去掉多餘的重複記錄。

代碼如下	複製代碼
select distinct name from table 得到的結果是: name a b c

好像達到效果了，可是，我想要得到的是id值呢？改一下查詢語句吧:

代碼如下	複製代碼
select distinct name, id from table 結果會是: id name 1 a 2 b 3 c 4 c 5 b

distinct怎麼沒起作用？作用是起了的，不過他同時作用了兩個欄位，也就是必須得id與name都相同的才會被排除。。。。。。。

我們再改改查詢語句:

select id, distinct name from table

現在將完整語句放出:

代碼如下	複製代碼
select *, count(distinct name) from table group by name 結果: id name count(distinct name) 1 a 1 2 b 1 3 c 1

上面簡單但有些地方是不能完成我們的需要的，下面記錄了些常用的重複記錄動作陳述式

查詢及重複資料刪除記錄的方法
(一)
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)