mysql實現(xiàn)查重只留一個的方法

這篇文章將為大家詳細(xì)講解有關(guān)mysql實現(xiàn)查重只留一個的方法，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

mysql實現(xiàn)查重只留一個的方法：首先通過“select * from”查找表中多余的重復(fù)記錄；然后通過“delete from”刪除重復(fù)數(shù)據(jù)，并只保留一個數(shù)據(jù)即可。

mysql 刪除重復(fù)數(shù)據(jù)只保留一條記錄

刪除重復(fù)數(shù)據(jù)保留name中id最小的記錄

delete from order_info where id not in (select id from (select min(id) as id from order_info group by order_number) as b);

delete from table where id not in (select min(id) from table group by name having count(name)>1) and  id in (select id group by name having count(name)>1)

(注意：HAVING 子句對 GROUP BY 子句設(shè)置條件的方式與 WHERE 和 SELECT 的交互方式類似。WHERE 搜索條件在進(jìn)行分組操作之前應(yīng)用；而 HAVING 搜索條件在進(jìn)行分組操作之后應(yīng)用。HAVING 語法與 WHERE 語法類似，但 HAVING 可以包含聚合函數(shù)。HAVING 子句可以引用選擇列表中顯示的任意項。)

擴展：

SQL：刪除重復(fù)數(shù)據(jù)，只保留一條用SQL語句,刪除掉重復(fù)項只保留一條在幾千條記錄里,存在著些相同的記錄,如何能用SQL語句,刪除掉重復(fù)的呢

1、查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個字段（peopleId）來判斷

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

2、刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個字段（peopleId）來判斷，只留有rowid最小的記錄

delete from people where   peopleName in (select peopleName    from people group by peopleName      having count(peopleName) > 1) and   peopleId not in (select min(peopleId) from people group by peopleName     having count(peopleName)>1)

3、查找表中多余的重復(fù)記錄（多個字段）

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

4、刪除表中多余的重復(fù)記錄（多個字段），只留有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、查找表中多余的重復(fù)記錄（多個字段），不包含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)

6.消除一個字段的左邊的第一位：

update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'

7.消除一個字段的右邊的第一位：

update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'

8.假刪除表中多余的重復(fù)記錄（多個字段），不包含rowid最小的記錄

update vitae set ispass=-1 where peopleId in (select peopleId from vitae group by peopleId,seq having count(*) > 1) and seq in （select 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)

關(guān)于mysql實現(xiàn)查重只留一個的方法就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。