表结构
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
效果