postgresql 合并列, 合并行

/ Postgres / 没有评论 / 205浏览

postgre 列合并

列的合并比较简单, 用到 array_agg

 CREATE TABLE public.test_table1
  (
    id integer, -- id
    name character varying(20), -- 姓名
    age integer -- 年龄
  ) 


INSERT INTO public.test_table1 (NAME, age)
VALUES
	('name1', 18),
	('name2', 19),
	('name3', 20),
	('name4', 21),
	('name5', 22);

合并列

  select array_agg(name), array_agg(age) from test_table1;

              array_agg            |    array_agg
---------------------------------+------------------
 {name1,name2,name3,name4,name5} | {18,19,20,21,22}
(1 行记录)

合并行使用到 row_to_json

  select row_to_json(t.*) from (
	select name, age from test_table1
  ) as t;

          row_to_json
---------------------------
 {"name":"name1","age":18}
 {"name":"name2","age":19}
 {"name":"name3","age":20}
 {"name":"name4","age":21}
 {"name":"name5","age":22}
(5 行记录)

如果要将多行多列合并成一行一列, 可以配合使用

  select array_agg(row_to_json(t.*)) from (
	select name, age from test_table1
  ) as t

  结果集被合并成JSON数组