Oracle中的四种去重方式
create table test(id int primary key not null,name varchar(10) not null,age int not null);insert into test values (1,'张三',20);insert into test values (2,'张三',20);insert into test values (3,'李四',20);in
·
create table test(
id int primary key not null,
name varchar(10) not null,
age int not null
);
insert into test values (1,'张三',20);
insert into test values (2,'张三',20);
insert into test values (3,'李四',20);
insert into test values (4,'李四',30);
insert into test values (5,'王五',40);
insert into test values (6,'王五',40);
commit;
1.distinct
select distinct name,age from test
2.group by
select name,age from test
group by name,age;
3.rowid(伪列去重)
select id,name,age from test t1
where t1.rowid in (select min(rowid) from test t2 where t1.name=t2.name and t1.age=t2.age);
4.窗口函数row_number () over()
select t.id ,t.name,t.age from
(select row_number() over(partition by name,age order by age) rank,test.* from test)t
where t.rank = 1;
以上就是Oracle中常见的四种去重方式,其中不推荐第一种方式,因为在数据量非常大的时候效率很低,在Mysql中8.0以前的版本是不支持窗口函数的,所以推荐使用group by 进行去重,8.0 以后就可以开开心心的使用窗口函数了。
更多推荐
已为社区贡献1条内容
所有评论(0)