关于row_number函数的用法

最新推荐文章于 2023-11-27 20:53:35 发布

天-外-飞-仙

最新推荐文章于 2023-11-27 20:53:35 发布

阅读量417

点赞数 1

分类专栏： ora

本文链接：https://blog.csdn.net/ribuluoywk/article/details/51956390

版权

ora 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

怎么在表ceshi中由：

id ids1 ids2 dates types
1 11 0 2004-4-7 1
2 11 0 2005-4-4 1
3 22 0 2005-4-4 1
4 22 0 2005-4-4 1
5 22 0 2005-4-4 1

得到：

id ids1 ids2 dates types
1 11 0 2004-4-7 1
3 22 0 2005-4-4 1

这里可以用row_number()函数实现，对ids1分组再根据id排序

drop table ceshi purge;

create table ceshi 
(
 id number,
 ids1 number,
 ids2 number,
 dates varchar2(10),
 types number
);

insert into ceshi values(1,11,0,'2004-4-7',1); 
insert into ceshi values(2,11,0,'2005-4-4',1);
insert into ceshi values(3,22,0,'2005-4-7',1);
insert into ceshi values(4,22,0,'2005-4-7',1);
insert into ceshi values(5,22,0,'2005-4-7',1);
select * from ceshi;

id ids1 ids2 dates types
1 11 0 2004-4-7 1
2 11 0 2005-4-4 1
3 22 0 2005-4-4 1
4 22 0 2005-4-4 1
5 22 0 2005-4-4 1

select * from (
select id,
       ids1,
       ids2,
       dates,
       types,
       row_number() over(partition by ids1 order by id) rn
  from ywk_ceshi
  ) where rn = 1

id ids1 ids2 dates types
1 11 0 2004-4-7 1
3 22 0 2005-4-4 1