一、排名

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

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

 

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

 

 

 二、分组后组内排名

/*分组普通排名：顺序排名*/SELECT city,score,rankFROM(SELECT cs.*,IF(@p=city,@r:=@r+1,@r:=1) AS rank,　　　　@p:=cityFROM cs,(SELECT @p:=NULL,@r:=0)rORDER BY city,score)s;

 

/* 分组后并列排名：组内相同数值排名相同*/SELECT city,score,rankFROM(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:=scoreFROM cs,(SELECT @p:=NULL,@s:=NULL,@r:=0)rORDER BY city,score )s;

 

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

/*取每组分数高的前两个，法一*/SELECT city,score,rankFROM(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:=scoreFROM cs,(SELECT @p:=NULL,@s:=NULL,@r:=0)rORDER BY city,score DESC )sWHERE rank <3;

/*分组后取前两个，法二*/SELECT * FROM cs cWHERE (    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