sql排序,组内排序

一、排名

/*普通排名：从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  #这里最好是将@p:=score 换成1或者TRUE,因为score如果为0或者null,则逻辑就会出错,建议单独给@p赋值,也就是在
END rank,
@p:=score   # 最好在这里赋值
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    //同第一个例子,最好写为1
    END,
   @r:=1 ) AS rank,
@p:=city,
@s:=score #不可少 ,当发生city改变的时候,IF里面并没有给@s赋值,
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 //同第一个例子,最好写为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

总结

:=是赋值,=是布尔值判断,当用 case when 时,最好不要用类似@s:=salary这种赋值表达式来作为布尔值控制流程,因为当salary是0或者null以及其他的false值的时候,与我们的控制流程可能不符.

另:当组内排序的时候,需要注意排序字段相同,以及分组字段的值发生变化的时候,排序字段的赋值,总之,最好在流程控制分支之外对排序字段的自定义参数以及分组字段的自定义参数进行赋值(赋当前记录的值);
自定义参数:用来记录上条字段的值,以便于通过与本条记录的值对比来进行排序