PostgreSQL：如何用单条查询获取关联的多对一对象及指定辅助数据？

问题背景 #

我在PostgreSQL中有三个关联表：

• objects：顶级表，包含id和name字段
• object_events：关联表，包含指向objects的外键object_id
• object_aux_data：与object_events为多对一关系，存储事件的辅助数据，字段为object_event（关联object_events.id）、name、value

示例数据：

• objects：id=1，name=test_object
• object_events：id=1、object_id=1；id=2、object_id=1
• object_aux_data：(1,'foo','foo_val')、(1,'bar','bar_val')、(2,'foo','foo_val2')、(2,'baz','baz_val')

需求是获取与test_object关联的object_events，并附带指定的辅助数据，输出格式如下：

注：所有object_event都有foo值，仅第一个object_event有bar值，baz无需纳入结果。

是否可以通过单条查询实现，而非为每个object_event单独查询？

解决方案 #

可以通过条件聚合实现单条查询获取目标结果，无需多次查询。利用CASE语句配合聚合函数将object_aux_data中的行数据转为列，具体SQL如下：

SELECT
    o.name AS "object.name",
    oe.id AS "object_event.id",
    MAX(CASE WHEN oad.name = 'foo' THEN oad.value END) AS foo,
    MAX(CASE WHEN oad.name = 'bar' THEN oad.value END) AS bar
FROM objects o
JOIN object_events oe ON o.id = oe.object_id
LEFT JOIN object_aux_data oad ON oe.id = oad.object_event
WHERE o.name = 'test_object'
GROUP BY o.name, oe.id
ORDER BY oe.id;

语句说明 #

1. 表关联：通过JOIN关联objects与object_events，再用LEFT JOIN关联object_aux_data，确保无bar值的事件也能被保留在结果中。
2. 行转列逻辑：用CASE语句筛选出指定name对应的value，再通过MAX聚合（因每个object_event对应同一name仅一条数据，MAX/MIN效果一致）将行数据转为列。
3. 分组排序：按object.name和object_event.id分组，保证每个事件对应一行结果，最后按事件ID排序。

执行该查询后即可得到需求格式的结果。

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