SQL> desc t * from t
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER(38)
TS                                                 VARCHAR2(10)

SQL> select * from t;

        ID TS
---------- ----------
         1 20061105
         1 20061101
         1 20061102
         1 20061103
         1 20061104
         1 20061105
         1 20061106
         1 20061107
         2 20061101
         2 20061103
         2 20061106
         2 20061107
         2 20061109
         2 20061110
         3 20061101
         3 20061103
         3 20061106
         3 20061107
         3 20061108
         3 20061109
         3 20061110
         3 20061111

22 rows selected.

SQL>

要取出大于五次,并且第五次的时间的 ID,TS

结果应该为
        ID TS
---------- ----------
         1 20061105
         2 20061109
         3 20061108

SQL> select a.id,a.ts from
2 (select id,ts,row_number() over (partition by id order by ts ) as rn from t ) a
3* where a.rn = 5

        ID TS
---------- ----------
         1 20061105
         2 20061109
         3 20061108

SQL> select a.id,a.ts from
2 (select id,ts,rank() over (partition by id order by ts ) as rn from t ) a
3 where a.rn = 5;

        ID TS
---------- ----------
         1 20061105
         1 20061105
         2 20061109
         3 20061108

SQL> select id,ts,rank() over (partition by id order by ts ) as rn from t ;

        ID TS                 RN
---------- ---------- ----------
         1 20061101            1
         1 20061102            2
         1 20061103            3
         1 20061104            4
         1 20061105            5
         1 20061105            5
         1 20061106            7
         1 20061107            8
         2 20061101            1
         2 20061103            2
         2 20061106            3
         2 20061107            4
         2 20061109            5
         2 20061110            6
         3 20061101            1
         3 20061103            2
         3 20061106            3
         3 20061107            4
         3 20061108            5
         3 20061109            6
         3 20061110            7
         3 20061111            8

SQL> select a.id,a.ts from
2 (select id,ts,dense_rank() over (partition by id order by ts ) as rn from t ) a
3 where a.rn = 5;

        ID TS
---------- ----------
         1 20061105
         1 20061105
         2 20061109
         3 20061108

SQL> select id,ts,dense_rank() over (partition by id order by ts ) as rn from t;

        ID TS                 RN
---------- ---------- ----------
         1 20061101            1
         1 20061102            2
         1 20061103            3
         1 20061104            4
         1 20061105            5
         1 20061105            5
         1 20061106            6
         1 20061107            7
         2 20061101            1
         2 20061103            2
         2 20061106            3
         2 20061107            4
         2 20061109            5
         2 20061110            6
         3 20061101            1
         3 20061103            2
         3 20061106            3
         3 20061107            4
         3 20061108            5
         3 20061109            6
         3 20061110            7
         3 20061111            8

SQL> select * from (
2 select * from (
3 select id,ts from (
4 select id,ts,dense_rank() over(partition by id order by ts) as tsrank from t
5 )
6 where tsrank=5
7 order by ts
8 )
9 union
10 select * from (
11 select id,ts from (
12 select id,ts,dense_rank() over(partition by id order by ts) as tsrank from t
13 )
14 where tsrank=5
15 order by ts
16 )
17 );

        ID TS
---------- ----------
         1 20061105
         2 20061109
         3 20061108

要取出大于五次,并且第五次的时间的 ID,TS ,同时选出前2个ID

SQL>select * from (
2 select id,ts from (
3 select id,ts,row_number() over(partition by id order by ts) as tsrank from t
4 )
5 where tsrank=5
6 order by ts
7* ) where rownum<=2

        ID TS
---------- ----------
         1 20061105
         3 20061108

SQL> select * from (
2 select id,ts from (
3 select id,ts from (
4 select id,ts,dense_rank() over(partition by id order by ts) as tsrank from t
5 )
6 where tsrank=5
7 )
8 union
9 select * from (
10 select id,ts from (
11 select id,ts,dense_rank() over(partition by id order by ts) as tsrank from t
12 )
13 where tsrank=5
14 )
15 order by ts
16 )
17 where rownum<=2;

        ID TS
---------- ----------
         1 20061105
         3 20061108

ITPub博客

[原创] 小议rank(),dense_rank(),row_number()使用与区别

最新文章