select查詢之五：分析函式在查詢的運用

在平時的查詢工作中，我們還通常會藉助一些分析函式，來協助查詢做分析。
以下就介紹幾個分析函式的例子。

基於TOP N：

1、資料背景表sc：

SQL> select * from sc;

       SNO        CNO      GRADE

---------- ---------- ----------

         1          1         91

         1          2         92

         1          3         93

         2          1         88

         2          2         92

         2          3         99

         3          1         65

         3          2         75

         3          3         85

         4          1         80

         4          2         88

       SNO        CNO      GRADE

---------- ---------- ----------

         4          3         93

12 rows selected.

2、獲取所有成績的top-3：

SQL> select grade

  2  from(select grade from sc

  3  order by grade desc)

  4  where rownum<=3;

     GRADE

----------

        99

        93

        93

3、每門課程成績的前三名；（注意：如果遇到了相同成績的情況，改如何考慮？）：

SQL> select * from(

  2  select sno,cno,grade,row_number() over

  3  (partition by cno order by grade desc)

  4  as rn from sc)

  5  where rn <> 4;

       SNO        CNO      GRADE         RN

---------- ---------- ---------- ----------

         1          1         91          1

         2          1         88          2

         4          1         80          3

         1          2         92          1

         2          2         92          2

         4          2         88          3

         2          3         99          1

         4          3         93          2

         1          3         93          3

9 rows selected.

4、查詢每門課程成績前三的資訊：

SQL> select * from(

  2  select sno,cno,grade,dense_rank() over

  3  (partition by cno order by grade desc)

  4  as rn from sc)

  5  where rn <> 4;

       SNO        CNO      GRADE         RN

---------- ---------- ---------- ----------

         1          1         91          1

         2          1         88          2

         4          1         80          3

         1          2         92          1

         2          2         92          1

         4          2         88          2

         3          2         75          3

         2          3         99          1

         4          3         93          2

         1          3         93          2

         3          3         85          3

11 rows selected.

5、請查詢出scott.emp表中根據工資（SAL欄位）從多到少排名，工資位於第5-第10的記錄：

SQL> select * from

  2  (select sal,row_number() over(order by sal desc)

  3  as rn

  4  from emp)

  5  where rn >=5

  6  and rn <=10;

       SAL         RN

---------- ----------

      2850          5

      2450          6

      1600          7

      1500          8

      1300          9

      1250         10

6 rows selected.

擴充：

1》cube函式按照月份，地區進行收入彙總

select earnmonth, area, sum(personincome)  

from earnings  

group by cube(earnmonth,area)  

order by earnmonth,area nulls last;

2》rollup函式按照月份，地區統計收入

select earnmonth, area, sum(personincome)  

from earnings  

group by rollup(earnmonth,area);

3》本文中還運用到了connect by rownum,這個引數是基於資料庫表中的rowid進行查詢的。