1、test表脚本
CREATE TABLE `test` (
`id` varbinary(32) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
)
2、插入数据
INSERT into test VALUES ('1','陈汇奇');
INSERT into test VALUES ('2','陈汇奇');
INSERT into test VALUES ('3','陈汇奇2');
INSERT into test VALUES ('4','陈汇奇');
INSERT into test VALUES ('5','陈汇奇2');
INSERT into test VALUES ('6','陈汇奇3');
3、现在需要做的是将重复的name删除,保留id最大的项:即删除id为1、2、3的数据
4、分析:
4.1、对名字进行分组:
SELECT `name` FROM test GROUP BY `name`
得到如下数据:
4.2、查看所有重复名字的id、姓名
SELECT * from test WHERE `name` in (SELECT `name` FROM test GROUP BY `name`)
-- 将查出来的数据作为 A 表
得到如下数据:
4.3、需要保留的项为:出现1次,及重复项id最大项
-- 对 A 表进行分组,查询 id 最大项
-- 简化SQL为:
SELECT
max( id ),name
FROM A
GROUP BY
`name`
-- 原SQL
SELECT
max( id ),name
FROM
( SELECT * FROM test WHERE `name` IN ( SELECT `name` FROM test GROUP BY `name`) ) A
GROUP BY
`name`
得到如下数据:
4.4、删除其余id即可
-- 简化SQL:
DELETE
FROM
test
WHERE
id NOT IN (
SELECT
max(id)
FROM A
GROUP BY `name` -- (该数据即为 4,5,6 三条数据)
)
-- 原SQL
DELETE
FROM
test
WHERE
id NOT IN (
SELECT
max(id)
FROM
( SELECT * FROM test WHERE `name` IN ( SELECT `name` FROM test GROUP BY `name` ) ) A
GROUP BY
`name`
)
select * from test;