多表关联后去重：优化SQL查询去除重复空值行

关联表结构 #

我们涉及的关联表结构如下：

items: id, zone_id
items_roles: role_id, item_id
zones: id
roles_zones: role_id, zone_id
roles: id, role_type_id

需求说明 #

要给items补充角色相关字段，规则很明确：

• 优先从items_roles关联的roles表取role_type_id和role_id
• 如果上述值为空，就从items关联的zone对应的roles_zones关联的roles表取兜底值

遇到的问题 #

一开始写的基础查询返回了14行结果，但有不少重复行：有些同一z_role_type_id和z_role_id的记录里，同时存在i_role_type_id/i_role_id为空和非空的情况，这显然不符合我们要优先保留非空值的需求，最终目标是得到7行有效结果。

优化后的查询语句 #

后来通过添加GROUP BY分组，配合MAX聚合函数来保留非空的优先字段，完美解决了重复问题，优化后的SQL如下：

SELECT items.id
,z_roles.role_type_id as z_role_type_id
,z_roles.id as z_role_id
,MAX(i_roles.role_type_id) as i_role_type_id
,MAX(i_roles.id) as i_role_id
FROM items
LEFT JOIN zones as j_zones ON j_zones.id = items.zone_id
LEFT JOIN roles_zones ON roles_zones.zone_id = j_zones.id
LEFT JOIN roles as z_roles ON (z_roles.id = roles_zones.role_id)
LEFT JOIN items_roles ON items_roles.item_id = items.id
LEFT JOIN roles as i_roles ON items_roles.role_id = i_roles.id AND (z_roles.role_type_id = i_roles.role_type_id)
WHERE items.id = 834
Group By items.id, z_roles.role_type_id, z_roles.id
ORDER BY items.id, i_role_id;

执行结果 #

执行后得到了完全符合需求的7行结果：

id | z_role_type_id | z_role_id | i_role_type_id | i_role_id
-----+----------------+-----------+----------------+-----------
834 | 5 | 111 | 5 | 68
834 | 11 | 120 | 11 | 120
834 | 7 | 77 | |
834 | 2 | 2 | |
834 | 12 | 91 | |
834 | 4 | 78 | |
834 | 8 | 36 | |
(7 rows)

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