sql 累计 总分 排名
表:
1.同分不同名(12345678)
SELECT obj.user_id,obj.score,@rownum := @rownum + 1 AS rownum FROM ( SELECT user_id, score FROM `sql_rank` ORDER BY score DESC ) AS obj, (SELECT @rownum := 0) r
2.同分同名不下移(122334566)
SELECT obj.user_id, obj.score, CASE WHEN @rowtotal = obj.score THEN @rownum WHEN @rowtotal := obj.score THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN @rownum :=@rownum + 1 END AS rownum FROM ( SELECT user_id, score FROM `sql_rank` ORDER BY score DESC ) AS obj, (SELECT @rownum := 0 ,@rowtotal := NULL) r
3.同分同名并下移(12245668)
SELECT obj_new.user_id, obj_new.score, obj_new.rownum FROM ( SELECT obj.user_id, obj.score, @rownum := @rownum + 1 AS num_tmp, @incrnum := CASE WHEN @rowtotal = obj.score THEN @incrnum WHEN @rowtotal := obj.score THEN @rownum END AS rownum FROM ( SELECT user_id, score FROM `sql_rank` ORDER BY score DESC ) AS obj, ( SELECT @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0 ) r ) AS obj_new