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进行查询的。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31392094/viewspace-2125910/ ，如需转载，请注明出处，否则将追究法律责任。
