在面试中遇到的一个问题
?
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 wheret1.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 //查询名字至少有一条的数据，并且只显示最小的主键号
?
?
?