MySQL 显示group分组后 某个字段的所有子项字段值

表结构

CREATE TABLE `t_log_role_room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL COMMENT '房间ID',
  `room_key` bigint(32) NOT NULL COMMENT 't_log_room对应房间的主键',
  `gold` bigint(32) NOT NULL COMMENT '房间内金币增减',
  PRIMARY KEY (`id`),
  KEY `role_id` (`role_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5537 DEFAULT CHARSET=utf8mb4;

sql语句

SELECT
	room_key AS room_id,
	GROUP_CONCAT(DISTINCT role_id) AS role_ids
FROM
	t_log_role_room
GROUP BY
	room_key

效果