mysql5.0字段排名、分组排名

最新推荐文章于 2024-03-25 08:40:13 发布

转载最新推荐文章于 2024-03-25 08:40:13 发布 · 648 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/3477605/blog/3036845

文章标签：

#数据库 #python

本文详细介绍了在MySQL中进行排名、分组排名以及如何选取各组前几名的方法。包括普通排名、并列排名的不同实现方式，以及在分组后的组内排名技巧。通过实例展示了如何使用SQL语句实现这些功能。

2019独角兽企业重金招聘Python工程师标准>>>

MySQL 排名、分组后组内排名、取各组的前几名

一、排名

/*普通排名：从1开始，顺序往下排*/
SELECT cs.*,@r :=@r + 1 AS rank
FROM cs,(SELECT @r := 0) r
ORDER BY score;

/*并列排名：相同的值是相同的排名*/
SELECT cs.* ,
CASE 
WHEN @p=score THEN @r
WHEN @p:=score THEN @r:=@r+1
END rank
FROM cs,(SELECT @r:=0,@p:=NULL)r
ORDER BY score;

/*并列排名：相同的值名次相同，与上例中的并列排名不同*/
SELECT city,score,rank
FROM
(
SELECT cs.*,
@c:=IF(@p=score,@c,@r) AS rank,
@p:=score,
@r:=@r+1
FROM cs ,(SELECT @p:=NULL,@r:=1,@c:=0)r
ORDER BY score
)c

二、分组后组内排名

/*分组普通排名：顺序排名*/
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;

/* 分组后并列排名：组内相同数值排名相同*/
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;

三、分组后取各组的前两名

/*取每组分数高的前两个，法一*/
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 DESC 
)s
WHERE rank <3;

/*分组后取前两个，法二*/
SELECT * FROM cs c
WHERE (
    SELECT count(*) FROM cs
    WHERE city=c.city AND score>c.score )<2
  ORDER BY city,score DESC

参考：

https://www.jianshu.com/p/bb1b72a1623e

http://blog.sina.com.cn/s/blog_4c197d420101e408.html

转载于:https://my.oschina.net/u/3477605/blog/3036845