需求:成就排行榜功能
实现:
简单介绍下表设计:
user_achievement 用户成就表
user 用户表
achievement 成就表
以下sql无序号返回:
SELECT SUM(a.achievement_num) top_number,b.nick_name,b.sex,b.birth_day,b.head_img
FROM user_achievement a
LEFT JOIN user b ON a.user_id = b.id
GROUP BY a.user_id
ORDER BY top_number desc
结果:
解决办法如下:
定义变量i,内连接初始化变量i为0,保证每次查询变量都会重新计算一次
SELECT t.*,(@i:=@i+1) AS id
FROM (SELECT SUM(a.achievement_num) top_number,b.nick_name,b.sex,b.birth_day,b.head_img
FROM user_achievement a
LEFT JOIN user b ON a.user_id = b.id
GROUP BY a.user_id
ORDER BY top_number DESC) t
INNER JOIN (SELECT @i:= 0) tt
结果: