PostgresQL SQL：将结果转换为数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5723851/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 10:12:41 来源:igfitidea点击:
PostgresQL SQL: Converting results to array
提问by DotNetDateQuestion
The query below:
查询如下：
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;
Gives me results like this:
给我这样的结果：
i_adgroup_id integer | i_category_id smallint
---------------------|-----------------------
15938 | 2
15938 | 3
15938 | 4
15942 | 1
15942 | 2
What I want is results like this:
我想要的是这样的结果：
i_adgroup_id integer | i_category_id smallint[]
---------------------|-----------------------
15938 | { 2, 3, 4 }
15942 | { 1, 2 }
How can I change the original SQL query to give me the result above?
如何更改原始 SQL 查询以提供上述结果？
回答by mu is too short
You want to use array_agg, this should work:
你想使用array_agg，这应该工作：
SELECT i_adgroup_id, array_agg(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;
Note that i_category_idis no longer in the GROUP BYas it is now being aggregated.
请注意，i_category_id它不再在 中，GROUP BY因为它现在正在聚合。
