Skip site navigation (1) Skip section navigation (2)

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

From: Kirill Simonov <xi(at)resolvent(dot)net>
To: pgsql-ru-general(at)postgresql(dot)org
Subject: Re: Вложенные SELECT и GROUP BY
Date: 2011-12-09 18:17:08
Message-ID: 4EE250A4.3060603@resolvent.net (view raw or flat)
Thread:
Lists: 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


Кирилл

In response to

Responses

pgsql-ru-general by date

Next:From: Dmitry E. OboukhovDate: 2011-12-09 18:52:27
Subject: Re: Вложенные SELECT и GROUP BY
Previous:From: Dmitry E. OboukhovDate: 2011-12-09 07:39:33
Subject: Вложенные SELECT и GROUP BY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group