mysql中的排名问题

在解决一些问题时，经常我们会遇到一些排名的问题，有很多种排名方式，如直接排名、分组排名，排名中相同的数排名相同后面的数连续或者后面的数不连续等等，这篇文章将总结几种mysql中的常见的排名问题。

一、排名
建表和数据：

-- 创建表
CREATE TABLE `scores` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `score` decimal(20,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_german2_ci;
-- 插入数据
insert  into `scores`(`id`,`score`) values 
(1,3.50),
(2,3.65),
(3,4.00),
(4,3.85),
(5,4.00),
(6,3.65);

就这么多数据

1.普通排名：从1开始，往下排

select 
  Score,
  @r := @r + 1 as rank 
from
  Scores,
  (select 
    @r := 0) r 
order by Score desc 

结果为：

2.根据分数排名，分数相同，名次相同，平分后的下一个名次应该是下一个连续的整数值

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

select 
  Score,
  (
    case
      when @p = Score 
      then @r 
      when @p := Score 
      then @r := @r + 1 
    end
  ) Rank 
from
  Scores,
  (select 
    @r := 0,
    @p := null) r 
order by Score desc 

结果为：

3.并列排名：相同的值排名相同，相同值的下一个名次应该是跳跃整数值

select 
  Score,
  rank 
from
  (select 
    Score,
    @c := if(@p = Score, @c, @r) as rank,
    @p := Score,
    @r := @r + 1 
  from
    Scores,
    (select 
      @p := null,
      @r := 1,
      @c := 0) r 
  order by Score desc) c 

结果为：

二、分组后组内排名
建表和数据：

-- 创建表
create table `cs` (
  `id` bigint (20) not null auto_increment,
  `city` varchar (10) default null,
  `score` int (11) default null,
  primary key (`id`)
) engine = innodb auto_increment = 14 default charset = utf8 ;
-- 插入数据
insert  into `cs`(`id`,`city`,`score`) values 
(1,'bj',40),
(2,'sh',20),
(3,'bj',40),
(4,'sh',20),
(5,'bj',60),
(6,'sh',75),
(7,'bj',80),
(8,'sh',66),
(9,'bj',78),
(10,'sh',50),
(11,'bj',70),
(12,'sh',40),
(13,'bj',40);

1.分组普通排名

select 
  city,
  score,
  rank 
from
  (select 
    cs.*,
    if(@p = city, @r := @r + 1, @r := 1) as rank,
    @p := city 
  from
    cs,
    (select 
      @p := null,
      @r := 0) r 
  order by city,
    score) s 

结果为：

2.分组后并列排名：组内相同数值排名相同

select 
  city,
  score,
  rank 
from
  (select 
    *,
    if(
      @p = city,
      case
        when @s = score 
        then @r 
        when @s := score 
        then @r := @r + 1 
      end,
      @r := 1
    ) as rank,
    @p := city,
    @s := score 
  from
    cs,
    (select 
      @p := null,
      @s := null,
      @r := 0) r 
  order by city,
    score) s

结果为：