总结几种MySQL中常见的排名问题
前言：
在某些应用场景中，我们经常会遇到一些排名的问题，比如按成绩或年龄排名。排名也有多种排名方式，如直接排名、分组排名，排名有间隔或排名无间隔等等，这篇文章将总结几种MySQL中常见的排名问题。
创建测试表
createtablescores_tb( idintauto_incrementprimarykey, xuehaointnotnull, scoreintnotnull )ENGINE=InnoDBDEFAULTCHARSET=utf8; insertintoscores_tb(xuehao,score)values(1001,89),(1002,99),(1003,96),(1004,96),(1005,92),(1006,90),(1007,90),(1008,94); #查看下插入的数据 mysql>select*fromscores_tb; +----+--------+-------+ |id|xuehao|score| +----+--------+-------+ |1|1001|89| |2|1002|99| |3|1003|96| |4|1004|96| |5|1005|92| |6|1006|90| |7|1007|90| |8|1008|94| +----+--------+-------+
1.普通排名
按分数高低直接排名，从1开始，往下排，类似于rownumber。下面我们给出查询语句及排名结果。
#查询语句 SELECTxuehao,score,@curRank:=@curRank+1ASrank FROMscores_tb,( SELECT@curRank:=0 )r ORDERBYscoredesc; #排序结果 +--------+-------+------+ |xuehao|score|rank| +--------+-------+------+ |1002|99|1| |1003|96|2| |1004|96|3| |1008|94|4| |1005|92|5| |1006|90|6| |1007|90|7| |1001|89|8| +--------+-------+------+
上述查询语句中，我们申明了一个变量@curRank,并将此变量初始化为0，查得一行将此变量加一，并以此作为排名。我们看到这类排名是没间隔的并且有些分数相同但排名不同。
2.分数相同，名次相同，排名无间隔
#查询语句 SELECTxuehao,score, CASE WHEN@prevRank=scoreTHEN@curRank WHEN@prevRank:=scoreTHEN@curRank:=@curRank+1 ENDASrank FROMscores_tb, (SELECT@curRank:=0,@prevRank:=NULL)r ORDERBYscoredesc; #排名结果 +--------+-------+------+ |xuehao|score|rank| +--------+-------+------+ |1002|99|1| |1003|96|2| |1004|96|2| |1008|94|3| |1005|92|4| |1006|90|5| |1007|90|5| |1001|89|6| +--------+-------+------+
3.并列排名，排名有间隔
另外一种排名方式是相同的值排名相同，相同值的下一个名次应该是跳跃整数值，即排名有间隔。
#查询语句 SELECTxuehao,score,rankFROM (SELECTxuehao,score, @curRank:=IF(@prevRank=score,@curRank,@incRank)ASrank, @incRank:=@incRank+1, @prevRank:=score FROMscores_tb,( SELECT@curRank:=0,@prevRank:=NULL,@incRank:=1 )r ORDERBYscoredesc)s; #排名结果 +--------+-------+------+ |xuehao|score|rank| +--------+-------+------+ |1002|99|1| |1003|96|2| |1004|96|2| |1008|94|4| |1005|92|5| |1006|90|6| |1007|90|6| |1001|89|8| +--------+-------+------+
上面介绍了三种排名方式，实现起来还是比较复杂的。好在MySQL8.0增加了窗口函数，使用内置函数可以轻松实现上述排名。
MySQL8.0利用窗口函数实现排名
MySQL8.0中可以利用ROW_NUMBER()，DENSE_RANK()，RANK()三个窗口函数实现上述三种排名，需要注意的一点是as后的别名，千万不要与前面的函数名重名，否则会报错，下面给出这三种函数实现排名的案例：
#三条语句对于上面三种排名 selectxuehao,score,ROW_NUMBER()OVER(orderbyscoredesc)asrow_rfromscores_tb; selectxuehao,score,DENSE_RANK()OVER(orderbyscoredesc)asdense_rfromscores_tb; selectxuehao,score,RANK()over(orderbyscoredesc)asrfromscores_tb; #一条语句也可以查询出不同排名 SELECTxuehao,score, ROW_NUMBER()OVERwAS'row_r', DENSE_RANK()OVERwAS'dense_r', RANK()OVERwAS'r' FROM`scores_tb` WINDOWwAS(ORDERBY`score`desc); #排名结果 +--------+-------+-------+---------+---+ |xuehao|score|row_r|dense_r|r| +--------+-------+-------+---------+---+ |1002|99|1|1|1| |1003|96|2|2|2| |1004|96|3|2|2| |1008|94|4|3|4| |1005|92|5|4|5| |1006|90|6|5|6| |1007|90|7|5|6| |1001|89|8|6|8| +--------+-------+-------+---------+---+
总结：
本文给出三种不同场景下实现统计排名的SQL，可以根据不同业务需求选取合适的排名方案。对比MySQL8.0，发现利用窗口函数可以更轻松实现排名，其实业务需求远远比我们举的示例要复杂许多，用SQL实现此类业务需求还是需要慢慢积累的。
以上就是总结几种MySQL中常见的排名问题的详细内容，更多关于MySQL排名的资料请关注毛票票其它相关文章！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。