最近和一个哥们聊起了他现在所做的项目,其向我大吐苦水!先说说他的情况,他现在做的是教育系统的项目,其中有一个业务是这样的,要给全校学生中每个科目的前三名发奖学金(所谓前三名就是考试成绩最好的三位了,这里不在赘述);他为这个sql 费了好长时间也没写出来,表的结构大体如下(无关字段省略):
id 科目 分数
1 语文 85
2 语文 53
3 语文 36
4 语文 56
5 语文 53
6 数学 85
7 数学 53
8 数学 36
9 数学 56
10 数学 53
..................当然还有科目和很多记录了,这里不在细说了;我就问他了那 不好办,你创建多几张表(每个科目一张),对每一张表
的科目进行order by然后取前面三条不就 ok了吗? 他说他也曾经这样想过,但被他们老大叼了一顿,他们老大的饿理由就是如果有100个科目的时候你是不是也建立100张表啊 ,是不是要执行100个 sql啊????...............很显然要表达的主要意思就是设计不好了!为此我查了好多资料终于搞定了,先顶一顶啊 ;大致的思路就是用oracle里面的rank()函数 ,以科目来分组，然后以分数来排序，给排序的结果分配rank，取前三名的rank
代码如下
1 建表语句
create table test_qjk_score(
stu int primary key,
subject varchar2(30),
mark int
);
insert into test_qjk_score(stu,subject,mark)values(1,'语文',85);
insert into test_qjk_score(stu,subject,mark)values(2,'语文',15);
insert into test_qjk_score(stu,subject,mark)values(3,'语文',25);
insert into test_qjk_score(stu,subject,mark)values(4,'语文',35);
insert into test_qjk_score(stu,subject,mark)values(5,'语文',45);
insert into test_qjk_score(stu,subject,mark)values(6,'语文',55);
insert into test_qjk_score(stu,subject,mark)values(7,'语文',65);
insert into test_qjk_score(stu,subject,mark)values(8,'语文',75);
insert into test_qjk_score(stu,subject,mark)values(9,'数学',83);
insert into test_qjk_score(stu,subject,mark)values(10,'数学',13);
insert into test_qjk_score(stu,subject,mark)values(11,'数学',23);
insert into test_qjk_score(stu,subject,mark)values(12,'数学',33);
insert into test_qjk_score(stu,subject,mark)values(13,'数学',43);
insert into test_qjk_score(stu,subject,mark)values(14,'数学',53);
insert into test_qjk_score(stu,subject,mark)values(15,'数学',63);
insert into test_qjk_score(stu,subject,mark)values(16,'数学',73);
insert into test_qjk_score(stu,subject,mark)values(17,'英语',87);
insert into test_qjk_score(stu,subject,mark)values(18,'英语',17);
insert into test_qjk_score(stu,subject,mark)values(19,'英语',27);
insert into test_qjk_score(stu,subject,mark)values(20,'英语',37);
insert into test_qjk_score(stu,subject,mark)values(21,'英语',47);
insert into test_qjk_score(stu,subject,mark)values(22,'英语',57);
insert into test_qjk_score(stu,subject,mark)values(23,'英语',67);
insert into test_qjk_score(stu,subject,mark)values(24,'英语',77);
执行
select * from (select rank() over(partition by subject order by mark desc) rk,test_qjk_score.* from test_qjk_score) T where T.rk<=3;
就可以得到结果了,结果如下:
RK STU SUBJECT MARK
1 9 数学 83
2 16 数学 73
3 15 数学 63
1 17 英语 87
2 24 英语 77
3 23 英语 67
1 1 语文 85
2 8 语文 75
3 7 语文 65
用row_number分析函数也是可以的
select * from (row_number() over(partition by subject order by mark desc) rk,S.* from S) T
where T.rk<=3;
oracle聚合函数rank()的用法和一些体会
SQL> select * from test_a;
ID PLAYNAME SCORE
-------------------- -------------------- ----------
01 aa 100
02 aa 101
02 bb 99
03 bb 98
04 aa 101
02 aa 101
需求是，将score降序排序，打印所有字段，并且如果是同一个playname的score只取出最高分，如果这个playname获得过多个相同的最高分，则只取出其中一个（比如：aa获得过3次101，则只取其中一个），最终要的结果就是：
RK ID PALYNAME SCORE
---------- -------------------- -------------------- ----------
1 02 aa 101
1 02 bb 99
本来我想用max函数，结果直接就出来了：
SQL> select max(score),palyname from test_a group by palyname;
MAX(SCORE) PALYNAME
---------- --------------------
101 aa
99 bb
但是要打印所有字段…OTL
即使用了嵌套，还是无法解决重复重现最高分的现象：
SQL> select distinct * from test_a t where score in (select max(score) from test_a group by palyname) order by score desc;
ID PALYNAME SCORE
-------------------- -------------------- ----------
02 aa 101
04 aa 101
02 bb 99
由于相同的playname对应的id不同，所以用distinct也无法过滤掉相同playname的并列最高分。
于是只好用rank（）了
Rank的基本语法为：
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
例子1：
TABLE：A （科目，分数）
数学，80
语文，70
数学，90
数学，60
数学，100
语文，88
语文，65
语文，77
现在我想要的结果是：（即想要每门科目的前3名的分数）
数学，100
数学，90
数学，80
语文，88
语文，77
语文，70
那么语句就这么写：
select * from (select rank() over(partition by 科目 order by 分数 desc) rk,a.* from a) t
where t.rk<=3;
以科目来分组，然后以分数来排序，给排序的结果分配rank，取前三名的rank
例子2：
有表Table内容如下
COL1 COL2
1 1
2 1
3 2
3 1
4 1
4 2
5 2
5 2
6 2
分析功能：列出Col2分组后根据Col1排序,并生成数字列。比较实用于在成绩表中查出各科前几名的信息。
SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM table a;
结果如下：
COL1 COL2 Rank
1 1 1
2 1 2
3 1 3
4 1 4
3 2 1
4 2 2
5 2 3
5 2 3
6 2 5
这个例子更直观一点，根据col2分组，根据clo1排序，我们可以发现：
5 2 3
5 2 3
6 2 5
即，如果两行记录完全相同，他们会被给予相同的rank，而排在它们之后的那行记录，由于前面的并列第3，使得之后的那条记录变成了第5，而如果我们在这里用的是dense_rank，那么之后的那条会变成第4
例子3：
合计功能：计算出数值(4,1)在Orade By Col1,Col2排序下的排序值，也就是col1=4,col2=1在排序以后的位置
SELECT RANK(4,1) WITHIN GROUP (ORDER BY col1,col2) "Rank" FROM table;
结果如下：
Rank
4
通过以上方法，得出col1为4，col2为1的那行数据的rank排名为多少
Dense_rank的例子：
dense_rank与rank()用法相当，但是有一个区别：dence_rank在并列关系是，相关等级不会跳过。rank则跳过
例如：表
A B C
a liu wang
a jin shu
a cai kai
b yang du
b lin ying
b yao cai
b yang 99
例如：当rank时为：
select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test3 m
A B C LIU
a cai kai 1
a jin shu 2
a liu wang 3
b lin ying 1
b yang du 2
b yang 99 2
b yao cai 4
而如果用dense_rank时为：
select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test3 m
A B C LIU
a cai kai 1
a jin shu 2
a liu wang 3
b lin ying 1
b yang du 2
b yang 99 2
b yao cai 3
那么再回到之前的那个需求，
SQL> select distinct * from (select rank() over(partition by playname order by score desc,id) rk,t.* from test_a t) where rk=1;
RK ID PLAYNAME SCORE
---------- -------------------- -------------------- ----------
1 02 aa 101
1 02 bb 99
这里order by score desc,id 以score降序和id这两个字段排序，也就是说，正因为相同的playname对应的id不同，这样相同的playname，相同的score，但是不同的id，这样的2行数据就获得了不同的rank，而rk=1，即是只取rank=1，也就是最高分。这样就完成了需求。
