题解 | #删除emp_no重复的记录，只保留最小的id对应的记录。#

看到这道题的时候，觉得不是特别难，不就是根据emp_no分组查一下最小的id，然后只要id不在这个范围内的都删除掉就行了，一执行，哦吼，sql报错了，原因是MySQL中不允许在子查询的同时删除本表数据

delete
from titles_test
where id not in (
    select min(id) as id
    from titles_test
    group by titles_test.emp_no
);

于是，你可以换种思路，将子查询的结果放入临时表中

delete
from titles_test
where id not in (
    select id
    from (
             select min(id) as id
             from titles_test
             group by titles_test.emp_no
         ) as a
);

还有一种思路：就是创建一个视图，将表titles_test的emp_no个数大于1的id放入该视图，然后删除的时候从视图查出要删除的id即可。

create view temp(id) as
(
select max(id)
from titles_test
group by emp_no
having count(*) > 1);

delete
from titles_test
where id in (select id from temp);