Oracle排名函数(Rank)实例详解
--已知：两种排名方式(分区和不分区):使用和不使用partition
--两种计算方式(连续,不连续),对应函数：dense_rank,rank
·查询原始数据：学号,姓名,科目名,成绩
select*fromt_score
·查询各科前2名(分区排名)
·类似：新闻表,求栏目点击率在前3位的新闻。
商品表,求各类别销售额在前10位的商品。
select*from( selectsc.s_id,sc.s_name,sub_name,sc.score, dense_rank()over (partitionbysub_nameorderbyscoredesc)名次 fromt_scoresc )x wherex.名次<=2
脚本：
createtablet_score ( autoidnumberprimarykey, s_idnumber(3), s_namechar(8)notnull, sub_namevarchar2(20), scorenumber(10,2) ); insertintot_score(autoid,s_id,s_name,sub_name,score) values(8,1,'张三','语文',80); insertintot_score(autoid,s_id,s_name,sub_name,score) values(9,2,'李四','数学',80); insertintot_score(autoid,s_id,s_name,sub_name,score) values(10,1,'张三','数学',0); insertintot_score(autoid,s_id,s_name,sub_name,score) values(11,2,'李四','语文',50); insertintot_score(autoid,s_id,s_name,sub_name,score) values(12,3,'张三丰','语文',10); insertintot_score(autoid,s_id,s_name,sub_name,score) values(13,3,'张三丰','数学',null); insertintot_score(autoid,s_id,s_name,sub_name,score) values(14,3,'张三丰','体育',120); insertintot_score(autoid,s_id,s_name,sub_name,score) values(15,4,'杨过','java',90); insertintot_score(autoid,s_id,s_name,sub_name,score) values(16,5,'mike','c++',80); insertintot_score(autoid,s_id,s_name,sub_name,score) values(3,3,'张三丰','oracle',0); insertintot_score(autoid,s_id,s_name,sub_name,score) values(4,4,'杨过','oracle',77); insertintot_score(autoid,s_id,s_name,sub_name,score) values(17,2,'李四','oracle',77); commit;