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 rank
FROM
    (
        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.*,
	(CASE
	WHEN @temp_view_count = ta.view_count THEN
	    @rank
	WHEN @temp_view_count := ta.view_count THEN
	    @rank := @rank + 1
	WHEN @temp_view_count = 0 OR @temp_view_count IS NULL THEN
	    @rank := @rank + 1
	END) AS rank
FROM
    (
        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 := (CASE
		WHEN @temp_view_count = ta.view_count THEN
		    @rank
		WHEN @temp_view_count := ta.view_count THEN
		    @index
		WHEN @temp_view_count = 0 OR @temp_view_count IS NULL THEN
		    @index
		END) 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实现了而已，逻辑增加，就得增加变量，都是一样的套路。