postgresql 在 postgres select 中，将列子查询作为数组返回？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31453151/
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
in postgres select, return a column subquery as an array?
提问by cc young
(have done this before, but memory fades, as does goggle)
（以前做过这个，但记忆消失了，护目镜也是如此）
wish to get select from userswith the tag.tag_ids for each user returned as an array.
希望从作为数组返回的每个用户users的tag.tag_ids 中进行选择。
select usr_id,
name,
(select t.tag_id from tags t where t.usr_id = u.usr_id) as tag_arr
from users u;
with the idea embedded query tag_arrwould be an array
与想法嵌入式查询tag_arr将是一个数组
回答by klin
Use the aggregate function:
使用聚合函数：
select
usr_id,
name,
array_agg(tag_id) as tag_arr
from users
join tags using(usr_id)
group by usr_id, name
or an array constructorfrom the results of a subquery:
或来自子查询结果的数组构造函数：
select
u.usr_id,
name,
array(
select tag_id
from tags t
where t.usr_id = u.usr_id
) as tag_arr
from users u
The second option is simple and fast while the first one is more generic, especially convenient when you need more than one aggregate from a related table.
第二个选项简单快捷，而第一个选项更通用，当您需要一个相关表中的多个聚合时尤其方便。
