(供自己学习)
查询全部的排名
SELECT
obj_new.user_id as userId,
obj_new.study_time as studyTime,
obj_new.rownum as rank
FROM
(
SELECT
obj.user_id,
obj.study_time,
@rownum := @rownum + 1 AS num_tmp,
@incrnum := CASE
WHEN @rowtotal = obj.study_time THEN
@incrnum
WHEN @rowtotal := obj.study_time THEN
@rownum
END AS rownum
FROM
(
SELECT
user_id,
study_time
FROM
`t_live_study`
ORDER BY
CAST(study_time AS UNSIGNED) DESC
) AS obj,
(
SELECT
@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0
) r
) AS obj_new
结果如下
查询单个的排名
SELECT
obj_new.user_id as userId,
obj_new.study_time as studyTime,
obj_new.rownum as rank
FROM
(
SELECT
obj.user_id,
obj.study_time,
@rownum := @rownum + 1 AS num_tmp,
@incrnum := CASE
WHEN @rowtotal = obj.study_time THEN
@incrnum
WHEN @rowtotal := obj.study_time THEN
@rownum
END AS rownum
FROM
(
SELECT
user_id,
study_time
FROM
`t_live_study`
ORDER BY
CAST(study_time AS UNSIGNED) DESC
) AS obj,
(
SELECT
@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0
) r
) AS obj_new
where user_id = #{userId}