面试遇到sql题目（二）

一、表结构及基础数据
create table customers(
    c_id number not null,
    o_id number not null,
    name varchar(50),
    age int
)
insert into customers values(1,2,'a',24);
insert into customers values(2,2,'a',24);
insert into customers values(3,2,'a',24);

insert into customers values(4,3,'b',23);
insert into customers values(5,3,'b',23);

insert into customers values(6,4,'c',25);

select * from customers

二、问题及参考答案   
--删除重复数据

方法1.

delete  from customers
       where c_id !=
    (select min(c_id) from customers c
    where c.o_id =customers.o_id
    and c.name = customers.name
    and c.age = customers.age )

方法2.

delete from customers
    where c_id not in 
    (select min(c_id) from customers group by o_id, name, age)

 

select * from customers

   
--设置表主键

alter table customers modify c_id primary key