以下查询:
SELECT i_adgroup_id, i_category_id
FROM adgroupcategories_br
WHERE i_adgroup_id IN
(
SELECT i_adgroup_id
FROM adgroupusers_br
WHERE i_user_id = 103713
)
GROUP BY i_adgroup_id, i_category_id;
结果如下所示:
i_adgroup_id integer | i_category_id smallint
---------------------|-----------------------
15938 | 2
15938 | 3
15938 | 4
15942 | 1
15942 | 2
我想要的是这样的结果:
i_adgroup_id integer | i_category_id smallint[]
---------------------|-----------------------
15938 | { 2, 3, 4 }
15942 | { 1, 2 }
我该如何修改原始的SQL查询以获得上述结果?
array_agg(array[c1, c2])
来构建数组的数组,或者使用派生表(select jsonb_object_agg(k, v) from (select k, array_agg(array[c1, c2]) v from ...) dt
)。或者现有的JSON函数可能会在不需要额外步骤的情况下完成它。具体取决于你想要生成什么。 - mu is too short