PostgreSQL的SQL：转换结果数组 [英] PostgresQL SQL: Converting results to array

问题描述

下面的查询：

    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 }

如何更改的原始SQL查询来给我上述结果？

How can I change the original SQL query to give me the result above?

推荐答案

您想要使用<一个href=\"http://www.postgresql.org/docs/current/static/functions-aggregate.html\"><$c$c>array_agg,这应该工作：

You want to use array_agg, this should work:

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;

注意 i_category_id 是集团不再BY ，因为它现在正在聚集。

Note that i_category_id is no longer in the GROUP BY as it is now being aggregated.

这篇关于PostgreSQL的SQL：转换结果数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！