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

SQL236. 删除emp_no重复的记录，只保留最小的id对应的记录。

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

原站题解

Sqlite 解法, 执行用时: 9ms, 内存消耗: 3304KB, 提交时间: 2021-06-08

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

Sqlite 解法, 执行用时: 9ms, 内存消耗: 3304KB, 提交时间: 2021-06-04

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

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3200KB, 提交时间: 2020-07-07

DELETE FROM titles_test WHERE id NOT IN 
(SELECT MIN(id) FROM titles_test GROUP BY emp_no)

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3276KB, 提交时间: 2020-11-10

/*方法一*/
delete from titles_test where id not in 
                                (select min(id)
                                 from titles_test
                                 group by emp_no)

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3296KB, 提交时间: 2021-07-16

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

详情