先附上官网上的一段,然后是自己写的
ROWNUM
For each row returned by a query,theROWNUM pseudocolumn 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 aROWNUM of 1,the second has 2,and so on.
You can useROWNUM to limit the number of rows returned by a query,as in this example:
SELECT * FROM employees WHERE ROWNUM
<
10;
If anORDERBY clause followsROWNUM in the same query,then the rows will be reordered by theORDERBY clause. The results can vary depending on the way the rows are accessed. For example,if theORDERBY clause 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 theORDERBY clause in a subquery and place theROWNUM condition in the top-level query,then you can force theROWNUM condition 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,theROWNUM values are those of the top-levelSELECT statement,so they are generated after the rows have already been ordered byemployee_id in the subquery.
Conditions testing forROWNUM values 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 aROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned aROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition,so no rows are returned.
You can also useROWNUM to 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; (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|