rownum浅析

先附上官网上的一段,然后是自己写的

ROWNUM

For each row returned by a query, theROWNUMpseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has aROWNUMof 1, the second has 2, and so on.

You can useROWNUMto limit the number of rows returned by a query, as in this example:

SELECT * FROM employees WHERE ROWNUM

<

10;

If anORDERBYclause followsROWNUMin the same query, then the rows will be reordered by theORDERBYclause. The results can vary depending on the way the rows are accessed. For example, if theORDERBYclause causes Oracle to use an index to access the data, then Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:

SELECT * FROM employees WHERE ROWNUM

<

11 ORDER BY last_name;

If you embed theORDERBYclause in a subquery and place theROWNUMcondition in the top-level query, then you can force theROWNUMcondition to be applied after the ordering of the rows. For example, the following query returns the employees with the 10 smallest employee numbers. This is sometimes referred to astop-Nreporting:

SELECT * FROM

(SELECT * FROM employees ORDER BY employee_id)

WHERE ROWNUM

<

11;

In the preceding example, theROWNUMvalues are those of the top-levelSELECTstatement, so they are generated after the rows have already been ordered byemployee_idin the subquery.

Conditions testing forROWNUMvalues greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM employees

WHERE ROWNUM

>

1;

The first row fetched is assigned aROWNUMof 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned aROWNUMof 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

You can also useROWNUMto assign unique values to each row of a table, as in this example:

UPDATE my_table

SET column1 = ROWNUM;

Please refer to the functionROW_NUMBERfor an alternative method of assigning unique numbers to rows.

查询前十个打卡的人

 select tt.*,rownum from (        
         select * from mydailydk dk         
         order by dk.dktime asc  ) tt
         where rownum <=10;

第是一个到第二十个打卡的人

 select * from (
   select tt.*,rownum rownum_ from (        
         select * from mydailydk dk         
         order by dk.dktime asc  ) tt
         where rownum <= 20)
         where rownum_ >10;    

上个功能用 row_num() 函数实现如下(主要用于 根据学科分组 取前几名 或者后几名等的时候用到)

 select * from (select id,currentday,name ,dktime,row_number() over (partition by name order by dktime asc) rownum_
 from mydailydk ) where rownum_ <=20 and rownum_ >10;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

数据迁移部分问题总结(r2第3天)

按照计划在周二开始了数据迁移,本来之前也做了不少的准备工作。但是还是在迁移的过程中出现了一些问题。简单做一个总结。 1.constraint导致的数据rej...

2757
来自专栏杨建荣的学习笔记

dataguard添加临时数据文件的bug(r7笔记第27天)

有一个环境是10gR2,一主两备,因为10g的备库还不是active,所以有一些查询的需求的时候,我们还是会打开相应的窗口时间。 开发的同学需要做一个大查询,数...

3257
来自专栏木宛城主

利用Microsoft.VisualBasic中TextFieldParser解析器把CSV格式倒入数据库

写了个Demo,利用Microsoft.VisualBasic这个程序集中的TextFieldParser解析器解析CSV格式的文件,然后将解析的数据插入到相...

17010
来自专栏数据之美

Hive 中内部表与外部表的区别与创建方法

先来说下Hive中内部表与外部表的区别: Hive 创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径, 不对数据的位...

2109
来自专栏数据和云

实战课堂:为什么更换存储之后一切正常但RAC集群启动不了?

这是一次来自生产实践的真实案例,某客户核心生产库由于进行新老存储替换变更操作后,Oracle RAC 两个节点均无法打开,数据库遭遇严重故障。

963
来自专栏杨建荣的学习笔记

通过shell脚本抓取awr报告中的问题sql(r6笔记第78天)

awr报告中的sql明细部分基本必看的部分,尤其是SQL Order by Elapsed time这个部分,能够很清晰的看到哪些sql语句占用了较多的DB t...

2513
来自专栏数据库新发现

在Mac上通过Docker部署Oracle Database 12.2版本

Oracle 已经宣布支持了Docker部署,这也让我们在Mac上部署 Oracle 数据库有了多一个选择,这是我的第一个Docker应用,非常简便快速的就完...

956
来自专栏数据结构与算法

BZOJ1046: [HAOI2007]上升序列(LIS)

  对于一个给定的S={a1,a2,a3,…,an},若有P={ax1,ax2,ax3,…,axm},满足(x1 < x2 < … < xm)且( ax1 < ...

843
来自专栏Java架构师历程

sql必会基础2

1、CREATE UNIQUE INDEX index_name ON table_name (

491
来自专栏杨建荣的学习笔记

一个oracle查询引起的bug (r4笔记第59天)

任何软件都不是完美的,oracle也是如此,隔一段时间就会收到oracle的邮件说建议打哪些安全补丁什么的。新发布的产品都是release 1,比如10gR1...

3686

扫码关注云+社区