转自：https://blog.csdn.net/weixin_41261833/article/details/103511400

大前提

假如你不懂mysql中“=”和“:=”的区别，需要去补习一下这两个知识的用法。

关于mysql中“=”和“:=”的区别，可以参考另外一篇文章。http://help.dwcpa.com.cn:8989/webdoc/real/Pub8a7e859d7668fdfa017c358afc8a7426.html?STATE=0&OPERATE=3

一、不分组排序

1.普通排名

从1开始，按照顺序一次往下排(相同的值也是不同的排名)。

-- 方法一

SELECT
m.*,@r :=@r + 1 AS rank
FROM
mian62 m,
(SELECT @r := 0) rorder BY score DESC;

-- 方法二

SET @r = 0;

SELECT
m.*,@r :=@r + 1 AS rank
FROM
mian62 m
ORDER BY
score DESC;

效果如下：

2.并列排名(不留空位)

相同的值是相同的排名(但是不留空位)。

-- 方法一

SELECT
c.sid,
c. NAME,
c.score,
c.rank
FROM
(
SELECT
m.*, 
@c := IF (@p = score ,@c ,@r) AS rank,
@p := score,
  @r :=@r + 1
FROM
mian62 m,
(SELECT @p := 0 ,@r := 1 ,@c := 0) r
ORDER BY
score DESC
) c

-- 方法二

SET @p = 0;


SET @r = 1;


SET @c = 0;

SELECT
c.sid,
c. NAME,
c.score,
c.rank
FROM
(
SELECT
m.*, @c := IF (@p = score ,@c ,@r) AS rank,
@p := score,
@r :=@r + 1
FROM
mian62 m
ORDER BY
score DESC
) c

效果如下：

3.并列排名（留空位）

相同的值是相同的排名(但是留空位)。

-- 方法一

SELECT
c.sid,
c. NAME,
c.score,
c.rank
FROM
(
SELECT
m.*,
      @c:=if(@p=score,@c,@r) as rank,
@p := score,
@r :=@r + 1
FROM
mian62 m,
(SELECT @p := 0 ,@r := 1 ,@c := 0) r
ORDER BY
score DESC
) c

-- 方法二

SET @p = 0;


SET @r = 1;


SET @c = 0;

SELECT
c.sid,
c. NAME,
c.score,
c.rank
FROM
(
SELECT
m.*, 
      @c:=if(@p=score,@c,@r) as rank,
@p := score,
@r :=@r + 1
FROM
mian62 m
ORDER BY
score DESC
) c

二、分组后排序

1.分组普通排名

从1开始，按照顺序一次往下排(相同的值也是不同的排名)。

SELECT
a.sid,
a. NAME,
a. SUBJECT,
a.score,
a.rank
FROM
(
SELECT
m.*,
if(@p=subject,@r:=@r+1,@r:=1) as rank,
@p := SUBJECT
FROM
mian62 m,
(SELECT @p := 0 ,@r := 0) r
ORDER BY
SUBJECT,
score DESC
) a;

效果如下：

2.分组后并列排名

组内相同数值排名相同。

-- 为了体现效果，修改其中一条数据；

update mian62 set score=58 where name="张三" and subject="化学";

-- 本题实现代码如下：

SELECT
a.sid,
a. NAME,
a. SUBJECT,
a.score,
a.rank
FROM
(
SELECT
*,
IF (
@p = SUBJECT,
CASE
WHEN @s = score THEN@r
WHEN @s := score THEN@r :=@r + 1
END,
@r := 1
) AS rank,
@p := SUBJECT,
@s := score
FROM
mian62 m,
(SELECT @p := 0 ,@s := 0 ,@r := 0) r
ORDER BY
SUBJECT,
score
) a;

效果如下：