资源描述：
《sql排名排序函数.docx》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、排名或顺序的函数ROW_NUMBER/RANK/DENSE_RANK/NTILE排名或顺序的函数ROW_NUMBER/RANK/DENSE_RANK/NTILE测试表/数据CREATETABLEtest_course(student_nameVARCHAR(10),--学生course_nameVARCHAR(10),--课程gradeINT--成绩);INSERTINTOtest_courseVALUES('甲','语文',95);INSERTINTOtest_courseVALUES('乙','语文',85);INSERTINTOtest_courseVALUES('丙
2、','语文',75);INSERTINTOtest_courseVALUES('丁','语文',65);INSERTINTOtest_courseVALUES('戊','语文',55);INSERTINTOtest_courseVALUES('己','语文',50);INSERTINTOtest_courseVALUES('庚','语文',60);INSERTINTOtest_courseVALUES('辛','语文',70);INSERTINTOtest_courseVALUES('壬','语文',80);INSERTINTOtest_courseVALUES('奎','
3、语文',90);INSERTINTOtest_courseVALUES('甲','数学',90);INSERTINTOtest_courseVALUES('乙','数学',80);INSERTINTOtest_courseVALUES('丙','数学',70);INSERTINTOtest_courseVALUES('丁','数学',60);INSERTINTOtest_courseVALUES('戊','数学',50);INSERTINTOtest_courseVALUES('己','数学',50);INSERTINTOtest_courseVALUES('庚','数学'
4、,60);INSERTINTOtest_courseVALUES('辛','数学',70);INSERTINTOtest_courseVALUES('壬','数学',85);INSERTINTOtest_courseVALUES('奎','数学',95);ROW_NUMBER顺序编号按照分数编号从高到底SELECTROW_NUMBER()OVER(ORDERBYSUM(grade)DESC)ASNO,student_name,SUM(grade)ASAllGradeFROMtest_courseGROUPBYstudent_nameORDERBYSUM(grade)DESC
5、1甲1852奎1853壬1654乙1655丙1456辛1407丁1258庚1209戊10510己100按照分数编号从高到底（区分课程）SELECTROW_NUMBER()OVER(PARTITIONBYcourse_nameORDERBYgradeDESC)ASNO,*FROMtest_courseORDERBYcourse_name,gradeDESC1奎数学952甲数学903壬数学854乙数学805丙数学706辛数学707庚数学608丁数学609戊数学5010己数学501甲语文952奎语文903乙语文854壬语文805丙语文756辛语文707丁语文658庚语文609戊语
6、文5510己语文50RANK排名不连续按照分数排名从高到底SELECTRANK()OVER(ORDERBYSUM(grade)DESC)ASNO,student_name,SUM(grade)ASAllGradeFROMtest_courseGROUPBYstudent_nameORDERBYSUM(grade)DESC1甲1851奎1853壬1653乙1655丙1456辛1407丁1258庚1209戊10510己100按照分数排名从高到底（区分课程）SELECTRANK()OVER(PARTITIONBYcourse_nameORDERBYgradeDESC)ASNO,*
7、FROMtest_courseORDERBYcourse_name,gradeDESC1奎数学952甲数学903壬数学854乙数学805丙数学705辛数学707庚数学607丁数学609戊数学509己数学501甲语文952奎语文903乙语文854壬语文805丙语文756辛语文707丁语文658庚语文609戊语文5510己语文50DENSE_RANK排名连续按照分数排名从高到底SELECTDENSE_RANK()OVER(ORDERBYSUM(grade)DESC)ASNO,student_name,SUM(grade
