假设user表中存在重复记录,如name为“tom”的用户存在重复记录,且age各不相同,现在想删除age比较小的user该如何操作?
id | name | age |
1 | tom | 20 |
2 | tom | 25 |
delete from user where id in(select * from (select min(age) from user group by name having count(*)>1)as a);
其中子查询需要设置别名如as a;子查询需要中间表,否则会报错。
(如有错误,欢迎拍砖。)