mysql - 用名称替换 GROUP_CONCAT 列中的 ID

Question

我用下面的 sql 查询成功连接了三个表

SELECT `bonuses`.`id`, `bonuses`.`bonus_name`, `bonuses`.`size`, creatorName.`name`, GROUP_CONCAT(DISTINCT bonus_user.user_id ORDER BY bonus_user.user_id SEPARATOR ', ') as bonusUsers from `bonuses`
inner join `users` as creatorName on `bonuses`.`created_from` = creatorName.`id`
inner join `bonus_user` on `bonuses`.`id` = `bonus_user`.`bonus_id`
group by `bonuses`.`id`

我得到的结果如下。作为下一步，我想用用户表中的名称替换“bonusUsers”列中的 ID。我该如何管理？

+----+--------------+------+--------------+--------------+
| id |  bonus_name  | size |     name     |  bonusUsers  |
+----+--------------+------+--------------+--------------+
| 3  | Bonus Test 3 |   5  |     Test1    |    1, 2, 3   |
| 4  | Bonus Test 4 |   3  |     Test1    |    1, 2, 3   |
+----+--------------+------+--------------+--------------+

用户

+----+-------+
| id | name  |
+----+-------+
| 1  | Test1 |
| 2  | Test2 |
| 3  | Test3 |
+----+-------+

奖金

+----+--------------+------+--------------+
| id |  bonus_name  | size | created_from |
+----+--------------+------+--------------+
| 1  | Bonus Test 1 |   1  |       1      |
| 2  | Bonus Test 2 |   1  |       1      |
| 3  | Bonus Test 3 |   5  |       1      |
| 4  | Bonus Test 4 |   3  |       1      |
+----+--------------+------+--------------+

bonus_user

+----+----------+------------+
| id | bonus_id | bonus_user |
+----+----------+------------+
| 1  |     3    |      1     |
| 2  |     3    |      2     |
| 3  |     3    |      3     |
| 4  |     4    |      1     |
| 5  |     4    |      2     |
| 6  |     4    |      3     |
+----+----------+------------+

Answer 1

再做一次与用户表的连接

SELECT `b`.`id`, `b`.`bonus_name`, `b`.`size`, u.`name`, 
GROUP_CONCAT(DISTINCT bu1.`name` ORDER BY bu.user_id SEPARATOR ', ') as bonusUsers 
from `bonuses` b
inner join `users` as u on `b`.`created_from` = u.`id`
inner join `bonus_user` bu on `b`.`id` = `bu`.`bonus_id`
inner join `users` as bu1 on `bu`.`user_id` = bu1.`id`
group by `b`.`id`, `b`.`bonus_name`, `b`.`size`, u.`name`

Demo