获取每类科目的前几条记录
今天有同事问一下sql语句的写法,以前见过是分两段做的,google了下,发现已经有人有简单的方案了。
本人已经测试过,是符合要求的。
文章的地址为:
代码如下
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
阅读(928) | 评论(1) | 转发(0) |