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” 的记录,只显示了一条。