mysql - 在MySQL连接查询中添加序列号

Question

我看到很多答案将序列号添加到简单的表中，但在这里我想将序列号添加到联接查询中。我尝试了这个查询

SELECT  *,@c:=@c+1 AS serialNumber 
FROM 
(SELECT @c:= 0) AS c,
(
  SELECT a.ai_id,a.ai_images,a.user_id,a.ai_like_count,a.ai_comment_count,a.ai_caption,u.display_name,u.first_name,u.email,a.ai_created 
  FROM album_images AS a 
  LEFT JOIN users AS u 
  ON a.user_id=u.user_id 
  WHERE u.email_validation=1 
  AND u.status=1 
  ORDER BY a.ai_created DESC
) AS t 
WHERE 1 
GROUP BY user_id 
ORDER BY ai_created DESC

但是我没有以正确的顺序获取数字，它变得像 1,5,8 这样。我想要这样 1,2,3,4

Answer 1

尝试这个查询

 set @c=0;
 SELECT @c:=@c+1 AS serialNumber,tab.* 
 FROM (
 SELECT * FROM
  (
   SELECT     a.ai_id,a.ai_images,a.user_id,a.ai_like_count,a.ai_comment_count,a.ai_caption,u.display_name,u.first_name,u.email,a.ai_created 
   FROM album_images AS a 
   LEFT JOIN users AS u ON a.user_id=u.user_id 
   WHERE u.email_validation=1 AND u.status=1 ORDER BY a.ai_created DESC) AS t GROUP BY user_id ORDER BY ai_created DESC
) AS tab