Mysql查询依据结果排名功能

Mysql查询依据结果排名功能

一、概述

工作中总会遇到一些排名的需求，逻辑也很简单，就是在排好序的数据上加上名次，大部分时候我都是在遍历数组的时候在结果上标注名次，今天就来聊聊直接通过mysql的sql功能实现名词。

二、准备数据

我就用很简单的表结构和数据来说说今天需要实现的功能，实际业务也许会复杂很多倍，不过都是同样的套路。

我就实现一下文章浏览量排名的功能。

建表

CREATE TABLE `dev_article_view` (  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,  `article_id` INT(10) NOT NULL COMMENT '文章id',  `view_count` BIGINT(20) DEFAULT NULL COMMENT '浏览量',  PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;

插入测试数据

INSERT INTO `dev_article_view`(article_id, view_count) VALUES(11, 100),(12, 90),(13, 80),(14, 70),(15, 60),(16, 50),(17, 40),(18, 30),(19, 20),(20, 10),(21, 0),(22, 90),(23, 60),(24, 60),(25, NULL);

完成后数据库的数据情况

三、实现功能

1、顺序排名

这种业务市最简单的，只需要顺序往下排就行了，如1 2 3 4 5，如果工作中真的需要实现这种功能，其实完全不必在sql层操作，在view层遍历显示列表的时候加上就好了。不过，为了咱们的功能循序渐进的实现，还是再此简单说明一下。

SELECT    ta.*, @rank := @rank + 1 AS rankFROM    (        SELECT article_id, view_count        FROM dev_article_view        ORDER BY view_count DESC    ) AS ta,    (SELECT @rank := 0) r

rank列就是名次了，sql的逻辑就是将rank变量初始化为0，然后每查出一条文章浏览量数据，将rank + 1并赋值给rank。

执行结果

2、同结果名次相同

上一种实现方式弊端很明显啊，浏览量一样，为什么你排名比我高，本着公平、公正、公开的原则，必须重新排名啊。先给出sql，稍后讲解。

SELECT ta.*,(CASEWHEN @temp_view_count = ta.view_count THEN    @rankWHEN @temp_view_count := ta.view_count THEN    @rank := @rank + 1WHEN @temp_view_count = 0 OR @temp_view_count IS NULL THEN    @rank := @rank + 1END) AS rankFROM    (        SELECT article_id, view_count        FROM dev_article_view        ORDER BY view_count DESC    ) AS ta,    (SELECT @rank := 0 ,@temp_view_count := NULL) r;

这次sql明显长了不少，有两个明显变化，第一是多初始化了一个temp_view_count变量，第二就是获得rank的逻辑多了3个判断。

rank意义还是名次，temp_view_count变量的作用是保存名次变化时的文章浏览量，也可以理解为上一条数据的文章浏览量，当浏览量和上一次一样市，rank是不变化的，不一样时，rank + 1，并将浏览量负值到temp_view_count，另外之所以加一下temp_view_count = 0或是NULL的情况，是因为mysql用户变量不能赋0或NULL。

执行结果

3、同结果排名相同并且占用名次

按道理第二种排名已经很公平了，但有时候只有公平是不不够的，比如我小时候考试80分，告诉老爸全班40人我考了全班第二名，而且是正数的；但实际情况是前面39人都是满分，我是倒数第一。所以有一些场景，我们是需要占用掉已经使用的名次。

    SELECT    temp.article_id,    temp.view_count,    temp.rank    FROM    (        SELECT            ta.*,            @index := @index + 1,    @rank := (CASEWHEN @temp_view_count = ta.view_count THEN    @rankWHEN @temp_view_count := ta.view_count THEN    @indexWHEN @temp_view_count = 0 OR @temp_view_count IS NULL THEN    @indexEND) AS rank        FROM        (    SELECT article_id, view_count    FROM dev_article_view    ORDER BY view_count DESC        ) AS ta,        ( SELECT @rank := 0 ,@rowtotal := NULL ,@index := 0 ) r    ) AS temp

这一次的套路就是增加一个index变量，只要有数据就加1，当temp_view_count不等于view_count时，就赋值index给rank，并将rank变量保存，当一样时，就继续使用rank。

执行结果

四、总结

综合看来，本文章就是将代码里面的排名功能使用sql实现了而已，逻辑增加，就得增加变量，都是一样的套路。