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 条评论
登录 后参与评论

相关文章

来自专栏数据库新发现

Linux上配置Unix ODBC连接Oracle数据库

Oracle10g前,Oracle并不提供UNIX/Linux下的ODBC驱动,用的最为广泛的是UnixOdbc,UnixOdbc的配置不算复杂,但是如果不顺利...

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

关于sysdba,sysoper,dba的区别(r3笔记第62天)

关于sysdba,sysoper,dba这些名词在工作中可能接触的比较多,如果接触的环境是服务器端的,sysdba可能是经常用到的。如果是数据库的维护工作,db...

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

使用impdp不当导致的数据丢失问题(r5笔记第1天)

今天有个朋友问我们一个问题,说他在使用了impdp导入数据的时候,使用了TABLE_EXISTS_ACTION=REPLACE这个选项,结果现在数据都给覆盖了。...

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

只言片语分析datapump的工作原理(r2第18天)

datapump是从oracle 10g推出的新的数据导入导出工具,可以说是exp/imp的加强版,主要的亮点在于服务端,结合了direct+parallel,...

2133
来自专栏微信公众号:Java团长

Spring boot Mybatis 整合

7.项目不使用application.properties文件 而使用更加简洁的application.yml文件: 将原有的resource文件夹下的app...

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

关于db link权限分配的苦旅(一) (r7笔记第42天)

昨天接到一个开发的需求,内容看起来非常简单。 申请数据库192.168.1.118:1522:TEST下用户APP_TE_FLOW_128赋予对表testore...

3326
来自专栏乐沙弥的世界

Oracle 控制文件(CONTROLFILE)

为二进制文件,初始化大小由CREATE DATABASE指定,可以使用RMAN备份

982
来自专栏cloudskyme

使用oracle的大数据工具ODCH访问HDFS数据文件

软件下载 Oracle Big Data Connectors:ODCH 下载地址: http://www.oracle.com/technetwork/bdc...

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

备库归档删除策略失效的问题分析 (r7笔记第6天)

最近碰到了一个有些奇怪的问题,自己当时排查问题时间紧,没有细细琢磨,今天抽空看了下,终于发现了端倪。 首先是在早晨收到了报警邮件,报警邮件内容如下: ZABBI...

3658
来自专栏Netkiller

怎样将 MySQL 数据表导入到 Elasticsearch

本文节选自《Netkiller Database 手札》 MySQL 导入 Elasticsearch 的方法有很多,通常是使用ETL工具,但我觉得太麻烦。于是...

5575

扫码关注云+社区