腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

軟件下載

android MAC 驅(qū)動(dòng)下載字體下載 DLL

源碼下載

PHP ASP.NET ASP JSP

軟件編程

C# JAVA C 語(yǔ)言 Delphi Android

網(wǎng)絡(luò)編程

PHP ASP.NET ASP JavaScript

在線工具

CSS格式化 JS格式化 Html轉(zhuǎn)化為Js

數(shù)據(jù)庫(kù)

MYSQL MSSQL oracle DB2 MARIADB

CMS

PHPCMS DEDECMS 帝國(guó)CMS WordPress

常用工具

PHP開(kāi)發(fā)工具 python Photoshop 必備軟件

MySQL 刪除數(shù)據(jù)庫(kù)中重復(fù)數(shù)據(jù)方法小結(jié)

更新時(shí)間：2014年07月08日 09:36:17 投稿：hebedich

在實(shí)際項(xiàng)目中，我們經(jīng)常會(huì)遇到刪除數(shù)據(jù)庫(kù)中重復(fù)數(shù)據(jù)的問(wèn)題，貌似是很簡(jiǎn)單的問(wèn)題哈，下面我們來(lái)探討下

剛開(kāi)始，根據(jù)我的想法，這個(gè)很簡(jiǎn)單嘛，上sql語(yǔ)句

delete from zqzrdp where tel in (select min(dpxx_id) from zqzrdp group by tel having count(tel)>1);

執(zhí)行，報(bào)錯(cuò)?。！~

異常意為：你不能指定目標(biāo)表的更新在FROM子句。傻了，MySQL 這樣寫，不行，讓人郁悶。

難倒只能分步操作，蛋疼

以下是網(wǎng)友寫的，同樣是坑爹的代碼，我機(jī)器上運(yùn)行不了。

1. 查詢需要?jiǎng)h除的記錄，會(huì)保留一條記錄。

select a.id,a.subject,a.RECEIVER from test1 a left join (select c.subject,c.RECEIVER ,max(c.id) as bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b on a.id< b.bid where a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id < b.bid

2. 刪除重復(fù)記錄，只保留一條記錄。注意，subject,RECEIVER 要索引，否則會(huì)很慢的。

delete a from test1 a, (select c.subject,c.RECEIVER ,max(c.id) as bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b where a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id < b.bid;

3. 查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來(lái)判斷

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

4. 刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來(lái)判斷，只留有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)

5.刪除表中多余的重復(fù)記錄（多個(gè)字段），只留有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)

看來(lái)想偷懶使用一句命令完成這個(gè)事好像不太顯示，還是老老實(shí)實(shí)的分步處理吧，思路先建立復(fù)制一個(gè)臨時(shí)表，然后對(duì)比臨時(shí)表內(nèi)的數(shù)據(jù)，刪除主表里的數(shù)據(jù)

alter table tableName add autoID int auto_increment not null; 

create table tmp select min(autoID) as autoID from tableName group by Name,Address; 

create table tmp2 select tableName.* from tableName,tmp where tableName.autoID = tmp.autoID; 

drop table tableName; 

rename table tmp2 to tableName;

您可能感興趣的文章: