说到去重复,感觉逻辑很简单。但动手写起来却并不是那么容易。面试的时候让写sql语句,然后没完整的写出来,然后面试官说你这没结果啊!
去重复首先要把要留下的选出来,然后删除其他重复项。distinct虽然好用,但规则不明显。这里采用:select max(id) from user group by 重复依据。
1.取出要留下的数据,本次依据重复项的最大id为保留项。
1
2
3
4
|
SELECT id, name ,age from person
WHERE id in (
SELECT MAX (id) from person GROUP BY name ,age
); |
2.放入临时表
1
2
3
4
5
|
DROP TABLE IF EXISTS temp1;
CREATE TEMPORARY TABLE temp1 SELECT id, name ,age from person
WHERE id in (
SELECT MAX (id) from person GROUP BY name ,age
); |
3.清空原表并从临时表注入
1
2
3
4
|
TRUNCATE person;
INSERT INTO person SELECT * from temp1;
DROP TABLE if EXISTS temp1;
SELECT * from person;
|
当然也可以直接delete:
1
2
3
4
5
|
DELETE from person WHERE id not in (
SELECT mid FROM (
SELECT MAX (id) mid from person GROUP BY name ,age
) t
) |
本文转自Ryan.Miao博客园博客,原文链接:http://www.cnblogs.com/woshimrf/p/5278683.html,如需转载请自行联系原作者