Mysql重複資料刪除資料

標籤：ja

一、準備測試表

CREATE TABLE `ts` (

  `id` INT(11) NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(30) DEFAULT NULL,

  `age` INT(11) DEFAULT NULL,

  `sex` VARCHAR(10) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

二、準備測試資料

insert into ts(name,age,sex) values(‘張三‘,10,‘男‘);

insert into ts(name,age,sex) values(‘張三‘,10,‘男‘);

insert into ts(name,age,sex) values(‘張三‘,10,‘女‘);

insert into ts(name,age,sex) values(‘李四‘,20,‘男‘);

INSERT INTO ts(NAME,age,sex) VALUES(‘李四‘,20,‘女‘);

insert into ts(name,age,sex) values(‘王五‘,20,‘男‘);

insert into ts(name,age,sex) values(‘王五‘,20,‘女‘);

INSERT INTO ts(NAME,age,sex) VALUES(‘王五‘,11,‘女‘);

INSERT INTO ts(NAME,age,sex) VALUES(‘王五‘,10,‘女‘);

INSERT INTO ts(NAME,age,sex) VALUES(‘郭靖‘,20,‘男‘);

INSERT INTO ts(NAME,age,sex) VALUES(‘郭靖‘,20,‘男‘);

INSERT INTO ts(NAME,age,sex) VALUES(‘歐陽鋒‘,20,‘男‘);

INSERT INTO ts(NAME,age,sex) VALUES(‘黃蓉‘,20,‘女‘);

INSERT INTO ts(NAME,age,sex) VALUES(‘郭富城‘,20,‘男‘);

INSERT INTO ts(NAME,age,sex) VALUES(‘黃藥師‘,20,‘男‘);

INSERT INTO ts(NAME,age,sex) VALUES(‘梅超風‘,20,‘女‘);

select * from ts;

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M02/82/C9/wKioL1dhBNLzcIuTAABVKVM2-_g440.jpg" title="11.jpg" alt="wKioL1dhBNLzcIuTAABVKVM2-_g440.jpg" />

三、需求，重複資料刪除資料，條件name和age一樣，就視為重複資料，對於重複的資料只需要保留一條

方法一、通過delete的方法

DELETE FROM ts  WHERE id IN (SELECT id FROM (SELECT  MAX(id) AS id,COUNT(*) AS COUNT FROM ts GROUP BY NAME,age HAVING COUNT >1 ORDER BY COUNT DESC) AS tab );

備忘：因為每次只重複資料刪除資料的最大id,所以如果重複資料有多條的情況，需要執行多次delete

select * from ts;

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M00/82/CA/wKiom1dhBDfSyZT7AAA69jqfH68224.jpg" title="12.jpg" alt="wKiom1dhBDfSyZT7AAA69jqfH68224.jpg" />

方式二、通過查詢的方法，先建好一張和原表一模一樣的表，包括主鍵，約束，索引都一樣的表，然後通過sql 把符合的結果查詢出來導到這個新表中，最後rename table.

create table ts1 like ts;-- 查詢的結果為不重複資料，union 重複的資料取主鍵最小的一條INSERT INTO ts1  select * from (select t.* from ts  t,-- 重複資料，取主鍵最小的一條(select  min(id) id, name,age , count(*) COUNT from ts group by name,age having count >1 order by count desc) b where t.id=b.id and t.name=b.name and t.age=b.age union SELECT t.* FROM ts  t, -- 不重複的資料，當然也要取出來了(SELECT  MAX(id) AS id,COUNT(*) AS COUNT FROM ts GROUP BY NAME,age HAVING COUNT =1 ) b where t.id=b.id) tm order by id

SELECT * FROM ts1;

得到的結果為,正好滿足條件

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M01/82/C9/wKioL1dhBaiCI761AAA69jqfH68544.jpg" title="12.jpg" alt="wKioL1dhBaiCI761AAA69jqfH68544.jpg" />

RENAME TABLE  ts TO ts_tmp;

RENAME TABLE ts1 TO ts;

SELECT * FROM ts;

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M01/82/C9/wKioL1dhBaiCI761AAA69jqfH68544.jpg" title="12.jpg" alt="wKioL1dhBaiCI761AAA69jqfH68544.jpg" />

本文出自 “小魚的部落格” 部落格，謝絕轉載！

Mysql重複資料刪除資料