重庆思庄Oracle、Redhat认证学习论坛

标题: 使用array_agg实现 PG16 下面的行变列 [打印本页]

作者: 郑全 时间: 2023-12-1 16:59
标题: 使用array_agg实现 PG16 下面的行变列

postgres=# create table test(id int,v int);
CREATE TABLE
postgres=# insert into test values(1,1);
INSERT 0 1
postgres=# insert into test values(1,2);
INSERT 0 1
postgres=# insert into test values(1,3);
INSERT 0 1
postgres=# insert into test values(2,20);
INSERT 0 1
postgres=# insert into test values(2,21);
INSERT 0 1
postgres=# insert into test values(2,22);
INSERT 0 1
postgres=# insert into test values(3,31);
INSERT 0 1
postgres=# insert into test values(3,32);
INSERT 0 1
postgres=# insert into test values(3,33);
INSERT 0 1
postgres=# select * from test;
id | v
----+----
  1 |  1
  1 |  2
  1 |  3
  2 | 20
  2 | 21
  2 | 22
  3 | 31
  3 | 32
  3 | 33
(9 rows)

postgres=# select id,array_agg(v) from test group by id;
id | array_agg
----+------------
  3 | {31,32,33}
  2 | {20,21,22}
  1 | {1,2,3}
(3 rows)

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)