如何实现单条房间记录关联最多3条成员记录的SQL查询

表结构 #

Room表 #

----------------
| id  |   name |  
----------------
|  1  |    A   |
----------------
|  2  |    B   |
----------------

Member表 #

--------------------------
| id | username | room_id |
--------------------------
| 1  | Johnson  |   1     |
--------------------------
| 2  | Laylar   |   1     |
--------------------------
| 3  | Myia     |   1     |
--------------------------
| 4  | Natalia  |   1     |
--------------------------
| 5  | Gusion   |   2     |
--------------------------
| 6  | Saber    |   2     |
--------------------------
| 7  | Cyclop   |   2     |
--------------------------
| 8  | Akai     |   1     |
--------------------------

期望查询结果 #

[
 {
   "id" : 1,
   "name" : "A",
   "members" : [
      {"id": 1,"username": "Johnson"},
      {"id": 2,"username": "Laylar"},
      {"id": 3,"username": "Myia"}
   ]
 },
 {
   "id" : 2,
   "name" : "B",
   "members" : [
      {"id": 5,"username": "Gusion"},
      {"id": 6,"username": "Saber"},
      {"id": 7,"username": "Cyclop"}
   ]
 }
]

尝试过的错误及问题 #

错误语句1：子查询返回多列 #

SELECT room.*, (SELECT member.id, member.username FROM member WHERE member.room_id = room.id LIMIT 3) AS members FROM room INNER JOIN member ON room.id = member.room_id;

报错信息：
ERROR 1241 (21000): Operand should contain 1 column(s)
原因：子查询返回了id和username两列，无法直接作为members字段的单一值。

错误语句2：GROUP_CONCAT搭配LIMIT无效 #

SELECT room.*, (SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', member.id, 'username', member.username) separator ','), ']') FROM member WHERE member.room_id = room.id LIMIT 3) AS members FROM room JOIN member ON room.id=member.room_id GROUP BY room.id;

问题：LIMIT 3并未生效，返回了房间的所有成员。原因是LIMIT放在子查询末尾仅限制整个子查询的返回行数，但GROUP_CONCAT会聚合所有符合member.room_id = room.id条件的行，而非前3行。

解决方案 #

方法1：MySQL 8.0+（窗口函数实现） #

利用ROW_NUMBER()窗口函数给每个房间的成员编号，筛选出前3个后再聚合为JSON数组：

SELECT 
    r.id,
    r.name,
    JSON_ARRAYAGG(JSON_OBJECT('id', m.id, 'username', m.username)) AS members
FROM 
    Room r
JOIN (
    SELECT 
        id,
        username,
        room_id,
        -- 按房间分区，给成员按id排序编号
        ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY id) AS rn
    FROM 
        Member
) m ON r.id = m.room_id AND m.rn <= 3
GROUP BY 
    r.id, r.name;

说明：JSON_ARRAYAGG是MySQL 5.7.22+支持的函数，可直接将多行数据聚合为JSON数组，无需手动拼接字符串，格式更可靠。

方法2：MySQL 5.x（变量实现行号） #

如果使用低版本MySQL，不支持窗口函数，可通过用户变量实现行号筛选：

SELECT 
    r.id,
    r.name,
    CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', m.id, 'username', m.username) SEPARATOR ','), ']') AS members
FROM 
    Room r
JOIN (
    SELECT 
        id,
        username,
        room_id,
        -- 切换房间时重置行号，否则递增
        @rn := IF(@current_room = room_id, @rn + 1, 1) AS rn,
        @current_room := room_id
    FROM 
        Member,
        -- 初始化变量
        (SELECT @current_room := 0, @rn := 0) vars
    ORDER BY 
        room_id, id
) m ON r.id = m.room_id AND m.rn <= 3
GROUP BY 
    r.id, r.name;

验证结果 #

执行上述任一语句，均可得到符合期望的查询结果，每个房间仅返回最多3个成员的JSON数组。

内容的提问来源于stack exchange，提问作者Nring Cham Aung