首页 > 试题广场 >

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

[编程题]删除emp_no重复的记录，只保留最小的id对应的记录。

热度指数：165846 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 32M，其他语言64M
算法知识视频讲解

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表为(注：最后会select * from 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

算法知识视频讲解

ciphersaw

本题思路如下：先用 GROUP BY 和 MIN() 选出每个 emp_no 分组中最小的 id，然后用 DELETE FROM ... WHERE ... NOT IN ... 语句删除 “非每个分组最小id对应的所有记录”

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

发表于 2017-07-22 15:25:24 回复(51)

牛客150668110号

 DELETE titles_test
 FROM titles_test
 LEFT JOIN
    (SELECT MIN(id) AS id
		FROM titles_test
    GROUP BY emp_no, title, from_date, to_date) AS min_id
 ON titles_test.id = min_id.id
 WHERE min_id.id IS NULL

发表于 2021-01-05 20:40:33 回复(0)

野生的桔子

本题高赞答案在MySQL中会出错。抛出异常：ERROR 1093 (HY000): You can't specify target table 'titles_test' for update in FROM clause。经查询，MySQL的UPDATE或DELETE中子查询不能为同一张表，可将查询结果再次SELECT。详见https://www.cnblogs.com/cuisi/p/7372333.html

修改后的代码为：

DELETE FROM titles_test
WHERE id NOT IN (
SELECT *
FROM(
SELECT MIN(id)
FROM titles_test
GROUP BY emp_no
) AS a);

这在OJ中也可执行。

另外，在MySQL中还有一个坑，需要给子查询添加别名，不然会抛出错误：ERROR 1248 (42000): Every derived table must have its own alias，详见：https://blog.csdn.net/cao478208248/article/details/28122113

发表于 2019-09-07 21:24:47 回复(19)

Java

指offer的剑在哪？

先按照emp_no对表进行分组，然后找到每个组最小的id号，然后进行判断删除

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

发表于 2017-09-01 01:57:52 回复(1)

练习时长两年半的Java练习生

### 正向思维：找出所有需要删除的记录的id

#### step1:找出所有出现的频次>1 emp_no 及最小id

SELECT MIN(id) AS id,emp_no FROM titles_test 
GROUP BY emp_no HAVING COUNT(emp_no) > 1

+------+--------+
|    1 | 10001 |
|    2 | 10002 |
|    3 | 10003 |
+------+--------+

#### step2:联合titles_test 找出所有需要删除的id

SELECT a.id FROM titles_test a ,
    (SELECT MIN(id) AS id,emp_no FROM titles_test 
        GROUP BY emp_no HAVING COUNT(emp_no) > 1) AS b
WHERE a.emp_no = b.emp_no AND a.id > b.id

+----+
| 5 |
| 6 |
| 7 |
+----+

#### step3:根据id删除数据

DELETE FROM titles_test WHERE id IN (
    SELECT id FROM(
                SELECT a.id FROM titles_test a ,
                   (SELECT MIN(id) AS id,emp_no FROM titles_test 
                    GROUP BY emp_no HAVING COUNT(emp_no) > 1) AS b
                WHERE a.emp_no = b.emp_no AND a.id > b.id )t
)

### 逆向思维：找出所有emp_no组中最小的那些id

#### step1: 找出所有emp_no组中最小的那些id

SELECT MIN(id) FROM titles_test GROUP BY emp_no

#### step2: 根据id删除数据

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

发表于 2019-05-06 15:05:36 回复(9)

qstalking

如何删除重复记录，指定保留的记录。
#删除指定数据的语句为 delete from 表名 where限制条件

#思路：不是emp_no分组中的最小id时，删除。因为emp_no不重复时，id唯一，在分组中既是最小值也是最大值。

语言运行环境：Sql(mysql 8.0)

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

结果报错：You can't specify target table 'titles_test' for update in FROM clause"
意思是说，不能先select出同一表中的某些值，然后在同一语句中更改这个表。

解决方法：把用到titles_test这个表的查询作为中间表，用from子查询再查一遍。where中括起来是不管用的。

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

发表于 2021-01-25 13:05:39 回复(8)

鹿鸣松

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

在mysql中这样写不行的，mysql不允许用一个表中查询出来的数据更新同一个表😂智熄的操作

改成

delete from titles_test where id not in(
select min_id from(
select min(id) as min_id from titles_test group by emp_no) a)
在更新操作之前再查一下就好了，对mysql来说，这道题给个中等难度都不为过啊😀

发表于 2020-12-26 18:27:22 回复(5)

BpointA

给一个前面没看到的解法

DELETE a
FROM titles_test a,titles_test b
WHERE a.emp_no=b.emp_no AND a.id>b.id

发表于 2021-04-27 11:12:25 回复(8)

dancee

需要审清题意，最后保留的是每一个emp_no下最小的id（一个emp_no对应多个id，只保留一个）

所以应该对 emp_no group by ,然后在分组中取每个组最小的min(id) 最后删除无关项 not in

发表于 2018-06-29 00:31:47 回复(0)

四月_

如果用人的思想代替机器，那么实现这个操作需要三步：1.根据emp_no分组，2.比较同组内id的大小，3.删除id大的数据。

收获是遇到读不懂的题目，要去实际用笔写下实现步骤，可能就会豁然开朗

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

发表于 2020-03-23 20:44:17 回复(2)

BWTNjIrMFoan

考点：删除语句的使用

思路：先查询出每个emp_no对应的最小id，在删除表中不在这些id中的记录

坑：mysql 中子查询和delete不允许同为一张表，需要将子查询再查询为另一张表，记得为外壳查询起个别名

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

编辑于 2021-03-27 14:44:38 回复(0)

Mysql

西冷牛排

1. 不能同时修改并查询同一张表，如果需要，则套一层子循环将这张表命名为另一张表即可

2. 要求保留同一emp_no下，id最小的行，可用group by分组去除重复的emp_no，然后求出每个emp_no下最小的id，然后删除不在这些最小id里面的行。

delete from titles_test

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

发表于 2022-09-14 02:25:11 回复(0)

CccAreYouOk

可用相关子查询做

delete from titles_test
where emp_no in (select tt.emp_no
      from titles_test tt
      where tt.emp_no = titles_test.emp_no 
      and tt.id > titles_test.id);

发表于 2019-09-26 09:50:36 回复(5)

1988coding

delete from titles_test where id in 
(
	/*得到应该删除的id结果集*/
    select id from 
    titles_test tt , 
    (/*查询出emp_no 对应的多个id的中的最小的id*/
		select emp_no,min(id) as min_id from titles_test
		group by emp_no
		having count(id) > 1
	) aa 
	where tt.id <> aa.min_id/*id 不是最小的,就是要删除的*/ and  tt.emp_no=aa.emp_no
);

发表于 2017-08-30 23:48:22 回复(1)

Mysql

堂小白

感觉像把SELECT换成DELETE

找到emp_no重复的列，删除id较大的。这个网站挺好的。

DELETE t1 FROM titles_test t1
JOIN titles_test t2
WHERE t1.id > t2.id AND t1.emp_no = t2.emp_no

发表于 2021-11-26 20:59:11 回复(0)

Mysql

LSur

# 这里需要把查询结果放到另外的临时表空间来解决"for update in FROM clause"这个报错，
DELETE FROM titles_test 
WHERE id in (
    select id 
    from (
           select a.id
           from titles_test a join titles_test b on a.emp_no = b.emp_no and a.id > b.id 
          ) tmp 
    );

发表于 2021-09-29 16:00:00 回复(0)

MournFear

DELETE FROM titles_test as a 
WHERE EXISTS (SELECT * FROM titles_test as s WHERE s.emp_no = a.emp_no and a.id > s.id)

使用EXISTS :存在比原表ID更小且emp_no一致的条目，就删除该条目

发表于 2020-07-14 16:44:00 回复(2)

向天歌123

mysql 中这样的代码就是执行不通，移植性太差

发表于 2019-06-14 16:27:55 回复(3)

AlanJiang

可以将按照id降序处理后的表分组的数据源，那么分组的且查询出的id结果是取每组中id最小的，然后将这个结果作为删除操作的列子查询，作为判断条件，这里用not in。

delete from titles_test

where id not in
(select id from
(select * from titles_test
order by id asc) as tt
group by emp_no);

发表于 2018-03-12 17:03:25 回复(0)

牛客403181168号

提供一个相对麻烦点的，使用了rank() over()

DELETE FROM titles_test

WHERE id IN (

SELECT id FROM (

SELECT id,RANK() OVER (PARTITION BY emp_no ORDER BY id) AS posn

FROM titles_test) AS tt

WHERE tt.posn = 2);

发表于 2023-09-01 16:30:58 回复(1)

提交观点

问题信息

SQL

难度：

303条回答 4302收藏 22528浏览

通过挑战的用户

查看代码

bbq了的大菠萝

2023-03-08 18:51:06
牛客51600...

2023-03-08 13:47:10
牛客81723...

2023-03-06 17:20:57
牛客28762...

2023-01-10 12:20:33
乌晓晰

2022-11-13 22:07:17

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

输入

输出

问题信息

热门推荐

通过挑战的用户

相关试题