MySQL中删除重复的数据，只保留id最小的那一行

1、先建表

CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30),
age INT 
);

INSERT INTO USER VALUES(NULL, 'a', 45);
INSERT INTO USER VALUES(NULL, 'b', 45);
INSERT INTO USER VALUES(NULL, 'c', 46);
INSERT INTO USER VALUES(NULL, 'd', 46);
INSERT INTO USER VALUES(NULL, 'e', 47);
INSERT INTO USER VALUES(NULL, 'f', 47);

2、如果数据库是Oracle，删除重复的age，保留id最小的一行

DELETE FROM USER WHERE age IN 
(SELECT age FROM USER GROUP BY age HAVING COUNT(age)>1)
AND id NOT IN (SELECT MIN(id)id FROM USER GROUP BY age HAVING COUNT(age)>1 )

3、如果是MySQL
使用上述语句会报错：You can’t specify target table ‘user’ for update in FROM clause
因为MySQL事务隔离级别高于Oracle，不允许查询过程中删除数据，所以需要在select过程中再加一张表
正确写法如下：
（在进行MIN(id) 搜索时，必须要取别名，即为MIN (id)id，因为不取别名查询的结果如下，字段名为MIN(id)

）

DELETE FROM USER WHERE age IN 
(SELECT age FROM (SELECT age FROM USER GROUP BY age HAVING COUNT(age)>1)a)
AND id NOT IN (SELECT id FROM (SELECT MIN(id)id FROM USER GROUP BY age HAVING COUNT(age)>1 )b)

最后结果为，每个age只保留了一行