• mysql之排名实现


    前言:mysql没有实现类似排名(rank)功能的函数。但是我们可以通过基数的查询加上其他函数可是实现类似的功能。

    题目:编写一个 SQL 查询来实现分数排名。

    一:首先我们创建一张并插入一些数据如下,用于方便后面排名的演示。

    CREATE TABLE `players` (
      `pid` int(2) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) NOT NULL,
      `age` int(2) NOT NULL,
      PRIMARY KEY (`pid`),
      UNIQUE KEY `name` (`name`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
     
    INSERT INTO `players` (`pid`, `name`, `age`) VALUES
    (1, 'Samual', 25),
    (2, 'Vino', 20),
    (3, 'John', 20),
    (4, 'Andy', 22),
    (5, 'Brian', 21),
    (6, 'Dew', 24),
    (7, 'Kris', 25),
    (8, 'William', 26),
    (9, 'George', 23),
    (10, 'Peter', 19),
    (11, 'Tom', 20),
    (12, 'Andre', 20);

    二:简单排名,相同的年纪随机分配排名次序

    SELECT pid, name, age, @curRank := @curRank + 1 AS rank
    FROM players p, (
    SELECT @curRank := 0
    ) q
    ORDER BY age

    解题分析:声明一个session级别的变量(注意在sql中声明变量需要在变量名前加@)。

    步骤一:在子查询中声明一个变量,并初始化为0。在select函数每记录一行数据变量加1(在select函数后‘:=’为赋值操作)。

    结果:

    | PID |    NAME | AGE | RANK |
    |-----|---------|-----|------|
    |  10 |   Peter |  19 |    1 |
    |  12 |   Andre |  20 |    2 |
    |   2 |    Vino |  20 |    3 |
    |   3 |    John |  20 |    4 |
    |  11 |     Tom |  20 |    5 |
    |   5 |   Brian |  21 |    6 |
    |   4 |    Andy |  22 |    7 |
    |   9 |  George |  23 |    8 |
    |   6 |     Dew |  24 |    9 |
    |   7 |    Kris |  25 |   10 |
    |   1 |  Samual |  25 |   11 |
    |   8 | William |  26 |   12 |

    备注1:我们通过子查询把声明变量和select函数通过一条语句完成(利用子查询)。我们也可以分离声明变量和查询分开如备注2

    备注2:分离声明变量和查询分,通过两条sql。

    sql1
    SET @curRank := 0;
    sql2
    SELECT pid, name, age, @curRank := @curRank + 1 AS rank
    FROM players
    ORDER BY age

    三:相同的分数需有相同的排名名次,排名无间隙

    SELECT pid, name, age, 
    CASE 
    WHEN @prevRank = age THEN @curRank 
    WHEN @prevRank := age THEN @curRank := @curRank + 1
    END AS rank
    FROM players p, 
    (SELECT @curRank :=0, @prevRank := NULL) r
    ORDER BY age

    结果

    | PID |    NAME | AGE | RANK |
    |-----|---------|-----|------|
    |  10 |   Peter |  19 |    1 |
    |  12 |   Andre |  20 |    2 |
    |   2 |    Vino |  20 |    2 |
    |   3 |    John |  20 |    2 |
    |  11 |     Tom |  20 |    2 |
    |   5 |   Brian |  21 |    3 |
    |   4 |    Andy |  22 |    4 |
    |   9 |  George |  23 |    5 |
    |   6 |     Dew |  24 |    6 |
    |   7 |    Kris |  25 |    7 |
    |   1 |  Samual |  25 |    7 |
    |   8 | William |  26 |    8 |

    解题分析:通过格外一个变量,记录上一条记录的年纪。通过比较当前年龄和该变量是否相同来判断排名是否加一。

    备注3:分析sql

    CASE 
        WHEN @prevRank = age THEN @curRank                     
        WHEN @prevRank := age THEN @curRank := @curRank + 1   
    END 

    第一个   WHEN @prevRank = age THEN @curRank  。 是判断语句,用于判断上一条数据的年纪和当前数据的年纪是否相等,如果为true,返回当前排名。如果为false,继续执行下一条WHEN

    第二个   WHEN @prevRank := age THEN @curRank := @curRank + 1 。是赋值,即把当前年龄赋值@prevRank(赋值操作总是返回为true),并排名加一。

    四:相同的分数需有相同的排名名次,排名有间隙

    SELECT pid, name, age, rank FROM
    (SELECT pid, name, age,
    @curRank := IF(@prevRank = age, @curRank, @incRank) AS rank, 
    @incRank := @incRank + 1, 
    @prevRank := age
    FROM players p, (
    SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
    ) r 
    ORDER BY age) s

    结果

    | PID |    NAME | AGE | RANK |
    |-----|---------|-----|------|
    |  10 |   Peter |  19 |    1 |
    |  12 |   Andre |  20 |    2 |
    |   2 |    Vino |  20 |    2 |
    |   3 |    John |  20 |    2 |
    |  11 |     Tom |  20 |    2 |
    |   5 |   Brian |  21 |    6 |
    |   4 |    Andy |  22 |    7 |
    |   9 |  George |  23 |    8 |
    |   6 |     Dew |  24 |    9 |
    |   7 |    Kris |  25 |   10 |
    |   1 |  Samual |  25 |   10 |
    |   8 | William |  26 |   12 |

    解题分析:在上一个sql的基础上再另加一个变量,用于记录select读取的行数.(即标题2中的排名)。并用三目运算符来判断使用哪个排名。

    备注:mysql中IF(expr1,expr2,expr3)函数是我们熟悉的三目运算函数。即If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3.

    参考1:http://fellowtuts.com/mysql/query-to-obtain-rank-function-in-mysql/

    参考2:https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_if

  • 相关阅读:
    Android端 配置极光推送
    udev的使用-minicom没有权限打开串口,更改 ttyUSB0 的权限
    【Oracle】RAC删除节点
    Linux学习(二) wget命令的使用
    Struts2拦截器
    SpringMVC实战(三种映射处理器)
    rbd_rados命令拷屏
    网络威胁防护,Azure 靠的是它?
    一块钱使用开发测试环境,助你快速打造万圣节促销应用
    想要快速搭建开发测试环境?这么做就可以!
  • 原文地址:https://www.cnblogs.com/jinliang374003909/p/12928995.html
Copyright © 2020-2023  润新知