Re: Вложенные SELECT и GROUP BY

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

Кирилл