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、复杂个人排名

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