程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql刪除重復數據記錄sql語句總結

mysql刪除重復數據記錄sql語句總結

編輯:MySQL綜合教程

刪除重復記錄的方法有很多種,這裡我來總結了各種各樣的刪除重啟記錄的sql語句,如:刪除id重復的數據,查找重復的,並且除掉最小的那個,刪除重復記錄,只保留一條記錄和刪除表中多余的重復記錄,重復記錄是根據單個字段(peopleId)來判斷,只留有rowid最小的記錄等等

我最常用的方法是

 代碼如下 復制代碼 //刪除id重復的數據,適合id是手工主鍵
delete person as a from person as a,
(
    select *,min(id) from person group by id having count(1) > 1
) as b
where a.id = b.id

//查找重復的,並且除掉最小的那個

 代碼如下 復制代碼 delete tb_person as a from tb_person as a,
(
select *,min(id) from tb_person  group by name having count(1) > 1
) as b
 where a.name = b.name and a.id > b.id;


好了下面再總結一些

1. 查詢需要刪除的記錄,會保留一條記錄。

 代碼如下 復制代碼

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. 刪除重復記錄,只保留一條記錄。注意,subject,RECEIVER 要索引,否則會很慢的。

 代碼如下 復制代碼

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. 查找表中多余的重復記錄,重復記錄是根據單個字段(peopleId)來判斷

 代碼如下 復制代碼

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


4. 刪除表中多余的重復記錄,重復記錄是根據單個字段(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)

5.刪除表中多余的重復記錄(多個字段),只留有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)

說了這麼多了我們來個例子吧

例子如下:

 代碼如下 復制代碼

drop table t_user;
create table t_user(
id        int(5) not null auto_increment,
username varchar(10),
age       int(3),
primary key(id)
);

insert into t_user(username,age) values('aaa',20);
insert into t_user(username,age) values('aaa',20);
insert into t_user(username,age) values('bbb',20);
insert into t_user(username,age) values('bbb',20);
insert into t_user(username,age) values('ccc',20);
insert into t_user(username,age) values('ccc',20);
insert into t_user(username,age) values('ddd',20);
insert into t_user(username,age) values('ddd',20);

mysql> select * from t_user;
+----+----------+------+
| id | username | age |
+----+----------+------+
| 1 | aaa      |   20 |
| 2 | aaa      |   20 |
| 3 | bbb      |   20 |
| 4 | bbb      |   20 |
| 5 | ccc      |   20 |
| 6 | ccc      |   20 |
| 7 | ddd      |   20 |
| 8 | ddd      |   20 |
+----+----------+------+

mysql> delete t_user from t_user , (select id from t_user group by username having count(*)>1 ) as t2 where t_user.id=t2.id;
Query OK, 4 rows affected (0.05 sec)


mysql> select * from t_user;
+----+----------+------+
| id | username | age |
+----+----------+------+
| 2 | aaa      |   20 |
| 4 | bbb      |   20 |
| 6 | ccc      |   20 |
| 8 | ddd      |   20 |
+----+----------+------+

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved