MYSQL添加行号错误

Question

我有这个sql

SET @rank:=0;

SELECT u.userid, a.referreid, c.avatar,c.avatarapproved, @rank:=@rank+1 AS rank , SUM( a.points ) as sum_points FROM 
ade39_alpha_userpoints_details AS a, ade39_alpha_userpoints as u ,ade39_findme as c 
WHERE (u.referreid = a.referreid) AND DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= a.insert_date 
AND (u.userid= c.user_id) AND (u.userid >0) 
GROUP BY a.referreid
 ORDER BY sum_points DESC LIMIT 200

它工作正常，但在为行号添加“排名”后，它“不起作用”=>>输出为空。当我删除“@rank:=@rank+1 ASrank”时，它正在工作

Answer 1

通常，您应该按选择字段列表中未与聚合函数一起使用的所有列进行分组。 MySQL 能够很好地处理它，但它可能无法处理变量。 (阅读手册 MySQL Handling of GROUP BY 部分的更多信息。)

尝试将原始查询嵌入到子查询中(但要注意顺序)。

SET @rank:=0;

SELECT @rank:=@rank+1 AS rank, * FROM (
  SELECT u.userid, a.referreid, c.avatar,c.avatarapproved, SUM( a.points ) AS sum_points
  FROM ade39_alpha_userpoints_details AS a,
       ade39_alpha_userpoints AS u,
       ade39_findme AS c 
  WHERE (u.referreid = a.referreid) AND
    DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= a.insert_date AND
    (u.userid= c.user_id) AND (u.userid >0) 
  GROUP BY a.referreid) T
ORDER BY sum_points DESC LIMIT 200