SQL42 删除emp_no重复的记录，只保留最小的id对应的记录——delete

描述

删除emp_no重复的记录，只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),

('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

删除后titles_test表为

id	emp_no	title	from_date	to_date
1	10001	Senior Engineer	1986-06-26	9999-01-01
2	10002	Staff	1996-08-03	9999-01-01
3	10003	Senior Engineer	1995-12-03	9999-01-01
4	10004	Senior Engineer	1995-12-03	9999-01-01

示例1

输入：

drop table if exists titles_test;
CREATE TABLE titles_test (
   id int(11) not null primary key,
   emp_no  int(11) NOT NULL,
   title  varchar(50) NOT NULL,
   from_date  date NOT NULL,
   to_date  date DEFAULT NULL);

insert into titles_test values
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

输出：

1|10001|Senior Engineer|1986-06-26|9999-01-01
2|10002|Staff|1996-08-03|9999-01-01
3|10003|Senior Engineer|1995-12-03|9999-01-01
4|10004|Senior Engineer|1995-12-03|9999-01-01

代码：

delete from titles_test 
where id not in (
    select * from (
        select min(id)
        from titles_test
        group by emp_no
    )tab
)

 或者

delete from titles_test 
where id not in(
    select id from (
        select id,
               row_number()over(partition by emp_no order by id asc) rk 
        from titles_test
    )tab 
    where rk = 1

知识点：

遭遇问题：you can't specify target table 'titles_test' for update in FROM clause
网查原因：在MYSQL里，不能先select一个表的记录，在按此条件进行更新和删除同一个表的记录，
解决办法：将select得到的结果，再通过中间表select一遍，这样就规避了错误，这个问题只出现于mysql，mssql和oracle不会出现此问题。