SQL刷题

牛客网刷题：SQL42 - 删除emp_no重复的记录，只保留最小的id对应的记录。

https://www.nowcoder.com/practice/3d92551a6f6d4f1ebde272d20872cf05?tpId=82&tqId=29810&rp=1&ru=/exam/oj&qru=/exam/oj&sourceUrl=%2Fexam%2Foj%3Ftab%3DSQL%25E7%25AF%2587%26topicId%3D82&difficulty=undefined&judgeStatus=undefined&tags=&title=
数据结构如下：

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');

1.第一步：筛选出id，group by根据语法要求，group by 后面的字段必须在select中包含，但是我用在select后面没有字段emp_no也可以，但是我在其他mysql语句中确实出现后报错。这个地方我不太清楚

select id from titles_test GROUP BY emp_no;

1.1实验观察发现

select id from titles_test GROUP BY emp_no的查询效果和SELECT MIN(id) FROM titles_test GROUP BY emp_no相同，在有相同emp_no的情况下，默认回筛选出min(id)的字段。而使用
SELECT MAX(id) FROM titles_test GROUP BY emp_no则不会得到 5，6，7,4

2.第二步：删除emp_no重复的记录

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

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

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