oracle中去掉某列重复的记录

1.准备测试表

创建测试表

create table temp_mul_tbl(       id varchar2(20) primary key,       name varchar2(50),       age int);

为表和字段添加注释

comment on table temp_mul_tbl is '测试表';comment on column temp_mul_tbl.id is '主键';comment on column temp_mul_tbl.name is '姓名';comment on column temp_mul_tbl.age is '年龄';

创建该表主键要用的序列

comment on table temp_mul_tbl is '测试表';comment on column temp_mul_tbl.id is '主键';comment on column temp_mul_tbl.name is '姓名';comment on column temp_mul_tbl.age is '年龄';

添加测试数据

insert into temp_mul_tbl(id, name, age)values(seq_temp_mul_tbl.nextval, 'lxl', 26);insert into temp_mul_tbl(id, name, age)values(seq_temp_mul_tbl.nextval, 'zhangsan', 26);insert into temp_mul_tbl(id, name, age)values(seq_temp_mul_tbl.nextval, 'lxl', 27);insert into temp_mul_tbl(id, name, age)values(seq_temp_mul_tbl.nextval, 'lisi', 28);insert into temp_mul_tbl(id, name, age)values(seq_temp_mul_tbl.nextval, 'wangwu', 28);insert into temp_mul_tbl(id, name, age)values(seq_temp_mul_tbl.nextval, 'lxl', 23);insert into temp_mul_tbl(id, name, age)values(seq_temp_mul_tbl.nextval, 'lxl', 24);

2.进行测试

执行以下sql，查询出所有的记录

seq_temp_mul_tbl

结果如下图所示

下面我们要去掉name重复的记录，sql如下：

select *  from temp_mul_tbl t where t.id in (select max(s.id) from temp_mul_tbl s where s.name = t.name);

执行结果如下：

和上面查询全部的结果相比较，已经去掉了name重复的记录， name 为 “lxl” 的记录，只显示了一条。