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

基於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

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進行查詢的。

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

相關文章