Re: Вложенные SELECT и GROUP BY - Mailing list pgsql-ru-general

On 12/09/2011 02:39 AM, Dmitry E. Oboukhov wrote:
>
> никак не привыкну к постгрисовым GROUP BY
>
> есть выборка:
>
> SELECT
>      "t1".*,
>      array_agg(t2.id) AS "t2_ids"
> FROM
>      "t1"
> LEFT JOIN "t2" ON "t2"."t1_id" = "t1"."id"
>
> GROUP BY
>      "t1"."id"
>
> LIMIT
>      100
> OFFSET
>      100
>


Можно спрятать ARRAY_AGG во вложенный SELECT, тогда проблемы не будет:

SELECT t1.*, COALESCE(t2.ids, '{}')
FROM t1
LEFT JOIN (SELECT t2.t1_id, ARRAY_AGG(t2.id) AS ids
            FROM t2 GROUP BY 1) AS t2 ON (t1.id = t2.t1_id)
LIMIT 100
OFFSET 100


Кирилл