分组后多行合并为一行：SQL多表关联角色字段取值问题

你当前的查询之所以返回多行且role_type_5_value取值错误，核心问题有两个：

1. 分组维度不对：你把z_roles.role_type_id和z_roles.id加入了GROUP BY，这会让每个zone角色单独生成一行结果，没法把所有role_type的聚合值合并到一行；
2. MAX函数的使用逻辑有误：在当前分组下，MAX只能取当前行的数值，没法跨行优先选取item级的角色值。

咱们换个思路：先分别把item级和zone级的角色按role_type_id聚合，再通过COALESCE优先取item级的角色ID，没有的话用zone级的兜底，最后把所有结果合并成一行。

修正后的查询语句 #

SELECT 
    i.id,
    -- 优先取item级的role_type=5角色，没有则取zone级的
    COALESCE(MAX(CASE WHEN r.role_type_id = 5 AND ir.item_id IS NOT NULL THEN r.id END),
             MAX(CASE WHEN zr.role_type_id = 5 THEN zr.id END)) AS role_type_5_value,
    -- 同理处理role_type=11
    COALESCE(MAX(CASE WHEN r.role_type_id = 11 AND ir.item_id IS NOT NULL THEN r.id END),
             MAX(CASE WHEN zr.role_type_id = 11 THEN zr.id END)) AS role_type_11_value,
    -- 同理处理role_type=7
    COALESCE(MAX(CASE WHEN r.role_type_id = 7 AND ir.item_id IS NOT NULL THEN r.id END),
             MAX(CASE WHEN zr.role_type_id = 7 THEN zr.id END)) AS role_type_7_value
FROM items i
LEFT JOIN zones z ON i.zone_id = z.id
-- 关联item专属角色
LEFT JOIN items_roles ir ON i.id = ir.item_id
LEFT JOIN roles r ON ir.role_id = r.id
-- 关联zone级兜底角色（用zr别名区分item角色）
LEFT JOIN roles_zones rz ON z.id = rz.zone_id
LEFT JOIN roles zr ON rz.role_id = zr.id
WHERE i.id = 834
GROUP BY i.id;

逻辑说明 #

1. 分别关联item的专属角色和所属zone的兜底角色，确保所有可能的角色数据都被拉取到；
2. 用CASE语句筛选出对应role_type_id的角色ID，同时通过ir.item_id IS NOT NULL标记出这是item级的角色；
3. COALESCE函数会优先取第一个非空值（也就是item级角色），如果item没有对应type的角色，就取zone级的兜底值；
4. 最后仅按items.id分组，确保结果只返回一行。

执行这个查询后，就能得到你期望的结果：

id | role_type_5_value | role_type_11_value | role_type_7_value
-----+-------------------+--------------------+-------------------
 834 | 68                | 120                | 77

如果后续需要支持更多role_type，只需要复制对应的COALESCE行，修改其中的role_type数值即可。

内容的提问来源于stack exchange，提问作者ksevelyar