相对复杂oracle的查询语句

     最近和一个哥们聊起了他现在所做的项目,其向我大吐苦水!先说说他的情况,他现在做的是教育系统的项目,其中有一个业务是这样的,要给全校学生中每个科目的前三名发奖学金(所谓前三名就是考试成绩最好的三位了,这里不在赘述);他为这个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

参考的文章为

http://downpour.iteye.com/blog/24445

http://space.itpub.net/13379967/viewspace-481811

对这两篇文章的作者表示感谢

 

 

 

rank()函数地球人都知道

row_number
1,2,3,4,5
rank
1,1,3,4,5
dense_rank
1,1,2,3,4