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

转载：https://www.cnblogs.com/niniya/p/9046449.html

一、排名

/*普通排名：从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