关闭

sql案例:删除多条内容相同的数据,只保留一条

446人阅读 评论(0) 收藏 举报
分类:

在面试中遇到的一个问题

 

create table t_delete_test (id number primary key,name varchar(20));

insert into t_delete_test (id,name) values(1,'a');
insert into t_delete_test (id,name) values(2,'b');
insert into t_delete_test (id,name) values(3,'b');
insert into t_delete_test (id,name) values(4,'a');
insert into t_delete_test (id,name) values(5,'c');
insert into t_delete_test (id,name) values(6,'c');
insert into t_delete_test (id,name) values(7,'d');

select * from t_delete_test;

 

 

 

        ID NAME

---------- --------------------

         1 a

         2 b

         3 b

         4 a

         5 c

         6 c

         7 d

 

 

delete from t_delete_test t1 where
t1.name in (select t2.name from t_delete_test t2 group by t2.name having count(1)>1)
and 
t1.id not in(select min(t3.id) from t_delete_test t3 group by t3.name having count(1)>1);

 

 

备注:

select t2.name from t_delete_test t2 group by t2.name having count(1)>1 //查询名字至少有一条相同的数据

select min(t3.id) from t_delete_test t3 group by t3.name having count(1)>1 //查询名字至少有一条的数据,并且只显示最小的主键号

 

 

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:310894次
    • 积分:8725
    • 等级:
    • 排名:第2259名
    • 原创:1096篇
    • 转载:14篇
    • 译文:0篇
    • 评论:5条
    最新评论