mysql - mysql 和 GROUP_CONCAT 中的增量计数器

Question

这是我的 mysql 查询:

SELECT users.id, user.name, category.id,
       GROUP_CONCAT(category.name) AS catName,
       @rownum := @rownum + 1 AS row_number
FROM users
JOIN usecat ON id_users=users.id
JOIN category ON id_category=category.id
CROSS JOIN (select @rownum := 0) r
GROUP BY users.id

结果:AAA-1、BBB-3、CCC-8、...

我想得到这个结果:AAA-1、BBB-2、CCC-3，...

你能帮我更改这个查询吗？谢谢。

Answer 1

试试这个:

SET @rownum=1;
SELECT *, @rownum := @rownum + 1 AS row_number 
FROM (SELECT users.id, user.name, category.id,
       GROUP_CONCAT(category.name) AS catName
  FROM users
  JOIN usecat ON id_users=users.id
  JOIN category ON id_category=category.id
GROUP BY users.id) t;