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只保留了一行