资源描述：
《oracle中聚合函数rank和denserank取科目前三名》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、oracle中聚合函数rank和dense_rank取科目前三名.txt13母爱是迷惘时苦口婆心的规劝；母爱是远行时一声殷切的叮咛；母爱是孤苦无助时慈祥的微笑。Rank的基本语法为：RANK()OVER([query_partition_clause]order_by_clause)1建表语句createtabletest_qjk_score(stuintprimarykey,subjectvarchar2(30),markint);insertintotest_qjk_score(stu,subject,mark)values(1,'语文',85);insertintotest_qjk_
2、score(stu,subject,mark)values(2,'语文',15);insertintotest_qjk_score(stu,subject,mark)values(3,'语文',25);insertintotest_qjk_score(stu,subject,mark)values(4,'语文',35);insertintotest_qjk_score(stu,subject,mark)values(5,'语文',45);insertintotest_qjk_score(stu,subject,mark)values(6,'语文',55);insertintotest_qj
3、k_score(stu,subject,mark)values(7,'语文',65);insertintotest_qjk_score(stu,subject,mark)values(8,'语文',75);insertintotest_qjk_score(stu,subject,mark)values(9,'数学',83);insertintotest_qjk_score(stu,subject,mark)values(10,'数学',13);insertintotest_qjk_score(stu,subject,mark)values(11,'数学',23);insertintotes
4、t_qjk_score(stu,subject,mark)values(12,'数学',33);insertintotest_qjk_score(stu,subject,mark)values(13,'数学',43);insertintotest_qjk_score(stu,subject,mark)values(14,'数学',53);insertintotest_qjk_score(stu,subject,mark)values(15,'数学',63);insertintotest_qjk_score(stu,subject,mark)values(16,'数学',73);insert
5、intotest_qjk_score(stu,subject,mark)values(17,'英语',87);insertintotest_qjk_score(stu,subject,mark)values(18,'英语',17);insertintotest_qjk_score(stu,subject,mark)values(19,'英语',27);insertintotest_qjk_score(stu,subject,mark)values(20,'英语',37);insertintotest_qjk_score(stu,subject,mark)values(21,'英语',47)
6、;insertintotest_qjk_score(stu,subject,mark)values(22,'英语',57);insertintotest_qjk_score(stu,subject,mark)values(23,'英语',67);insertintotest_qjk_score(stu,subject,mark)values(24,'英语',77);2.执行select*from(selectrank()over(partitionbysubjectorderbymarkdesc)rk,test_qjk_score.*fromtest_qjk_score)TwhereT.r
7、k<=3;3.就可以得到结果了,结果如下:RKSTUSUBJECTMARK19数学83216数学73315数学63117英语87224英语77323英语6711语文8528语文7537语文65dense_rank与rank()用法相当，但是有一个区别：dence_rank在并列关系是，相关等级不会跳过。rank则跳过 例如：表 A B C a liu wang a
