1、个人所在排行sql(单表简单排名)
SELECT
m.rank ,
m.user_id ,
m.weight
FROM
(
SELECT
(@i :=@i + 1) rank ,
user_id ,
weight
FROM
t_pig_user ,
(SELECT @i := 0) AS it
ORDER BY
weight DESC ,
insert_time ASC
) m
WHERE
m.user_id = 444
2、排行榜前99名(单表简单排名)
SELECT
(@i :=@i + 1) rank ,
user_id ,
weight
FROM
t_pig_user ,
(SELECT @i := 0) AS it
ORDER BY
weight DESC ,
insert_time ASC
LIMIT 99
3、复杂个人排名
SELECT
n.rank ,
u.uid ,
n.user_id ,
n.stepsToall ,
n.today_praise AS praise ,
u.NICK_NAME ,
u.USER_PIC_PATH
FROM
(
SELECT
m.rank ,
m.user_id ,
m.steps + m.prop_steps AS stepsToall ,
m.today_praise
FROM
(
SELECT
(@i :=@i + 1) rank ,
user_id ,
steps ,
prop_steps ,
today_praise
FROM
t_walk_user_step_today ,
(SELECT @i := 0) AS it
WHERE
today_date = #{todayTime}
ORDER BY
steps + prop_steps DESC ,
user_id ASC
) m
WHERE
m.user_id = #{userId}
) n
LEFT JOIN t_user u ON u.USER_ID = n.user_id
4、复杂排行
SELECT
m.rank ,
u.uid ,
m.user_id ,
m.stepsToall ,
u.NICK_NAME ,
u.USER_PIC_PATH ,
m.today_praise AS praise
FROM
(
SELECT
(@i :=@i + 1) rank ,
user_id ,
steps + prop_steps AS stepsToall ,
today_praise
FROM
t_walk_user_step_today ,
(SELECT @i := 0) AS it
WHERE
today_date = #{todayTime}
AND steps + prop_steps > 0
ORDER BY
steps + prop_steps DESC ,
user_id ASC
LIMIT 99
) m
LEFT JOIN t_user u ON u.USER_ID = m.user_id