例如:
-- 19、按各科成绩进行排序,并显示排名(实现不完全)
数据为:
SQL为:
SELECT
dt.c_id,dt.s_id,dt.s_score
,@r := CASE WHEN @u = dt.c_id and s_score!=@s THEN ifnull(0/(@s:=s_score),0) + @r + 1
WHEN @u = dt.c_id and s_score=@s THEN @r
WHEN @u := dt.c_id /* Notice := instead of = */ THEN 1
END AS user_game_rank
FROM ( SELECT s_id ,c_id, s_score FROM score ORDER BY c_id, s_score DESC) AS dt,(select @r := 0, @u := '01',@s:=0) b
;
排序后结果为: