在postgres选择，返回列子查询作为数组吗？ [英] in postgres select, return a column subquery as an array?

问题描述

（以前已经做过，但是记忆消失了，像护目镜一样）

(have done this before, but memory fades, as does goggle)

希望从个用户并以数组形式返回每个用户 tag.tag_id s。

wish to get select from users with the tag.tag_ids for each user returned as an array.

select usr_id,
       name,
       (select t.tag_id from tags t where t.usr_id = u.usr_id) as tag_arr
from   users u;

具有想法嵌入式查询 tag_arr 将是一个数组

with the idea embedded query tag_arr would be an array

推荐答案

使用集合函数：

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 constructor from 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.

这篇关于在postgres选择，返回列子查询作为数组吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！