From: | zab08 <zab08(at)126(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | PostgreSQL group sort |
Date: | 2011-02-25 06:15:24 |
Message-ID: | 74ac9f7c.6600.12e5b751c32.Coremail.zab08@126.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
use these sqls:
CREATE TABLE big(id serial, name text);
CREATE TABLE sm(id serial, big_id integer, name text);
INSERT into big (id, name) VALUES (1, 'big1');
INSERT into big (id, name) VALUES (2, 'big2');
INSERT into sm(id, big_id, name)VALUES (2, 1, 'sm1');
INSERT into sm(id, big_id, name)VALUES (1, 1, 'sm2');
INSERT into sm(id, big_id, name)VALUES (3, 1, 'sm3');
INSERT into sm(id, big_id, name)VALUES (6, 2, 'sm4');
INSERT into sm(id, big_id, name)VALUES (4, 2, 'sm5');
INSERT into sm(id, big_id, name)VALUES (4, 2, 'sm6');
--------------------------------
run :
SELECT b.id, array_accum(s.id), array_accum(s.name)from big b, sm s where b.id = s.big_id group by b.id;
(ps: array_accum is aggregate inhttp://www.postgresql.org/docs/9.0/static/xaggr.html)
id | array_accum | array_accum
----+-------------+---------------
1 | {2,1,3} | {sm1,sm2,sm3}
2 | {6,4,4} | {sm4,sm5,sm6}
(2 rows)
the excepted result:
id | array_accum | array_accum
----+-------------+---------------
1 | {1,2,3} | {sm1,sm2,sm3}
2 | {4,5,6} | {sm4,sm5,sm6}
(2 rows)
It is a group sort ?
From | Date | Subject | |
---|---|---|---|
Next Message | Yang Zhang | 2011-02-25 06:55:23 | Compression hacks? |
Previous Message | zab08 | 2011-02-25 06:02:48 | PostgresQL group sort |