mysql 查询排行_通过mysql查询排行榜
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、復(fù)雜個人排名
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、復(fù)雜排行
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
總結(jié)
以上是生活随笔為你收集整理的mysql 查询排行_通过mysql查询排行榜的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mysql 整形转换_mysql IP地
- 下一篇： windows7桌面没有反应怎么办 解决