[postgresql]postgresql的聚合函数sql实例

drop table if exists tree_test;
CREATE TABLE tree_test
(
id character varying(3),
pid character varying(3),
name character varying(50)
);

truncate table tree_test;
INSERT INTO tree_test(id,pid,name) values('001','0','深圳市');
INSERT INTO tree_test(id,pid,name)  values('002','001','南山区');
INSERT INTO tree_test(id,pid,name)  values('003','001','龙华区');
INSERT INTO tree_test(id,pid,name)  values('004','001','宝安区');
INSERT INTO tree_test(id,pid,name)  values('005','002','粤海街道');
INSERT INTO tree_test(id,pid,name)  values('006','002','前海街道');
INSERT INTO tree_test(id,pid,name)  values('007','002','深圳湾');

select * from tree_test ;

拼接concat_ws,没有分隔符
select concat_ws(id,pid,name) as con from tree_test

拼接concat_ws，用/分隔
select concat_ws('/',id,pid,name) as con from tree_test;

拼接concat_ws，用/分隔
select pid,group_concat(name) as con from tree_test group by pid;

用array_to_string组合
SELECT * ,array_to_string(array(SELECT att.name FROM tree_test att),'/') FROM tree_test;
 id  | pid |  name  |                 array_to_string
-----+-----+--------+--------------------------------------------------
 001 | 0   | 厦门市 | 未来海/青春海/厦门市/集美区/同安区/海沧区/区政府
 003 | 001 | 集美区 | 未来海/青春海/厦门市/集美区/同安区/海沧区/区政府
 007 | 002 | 未来海 | 未来海/青春海/厦门市/集美区/同安区/海沧区/区政府
 002 | 001 | 海沧区 | 海沧区/区政府/未来海/同安区/青春海/厦门市/集美区
 005 | 002 | 区政府 | 海沧区/区政府/未来海/同安区/青春海/厦门市/集美区
 004 | 001 | 同安区 | 未来海/青春海/同安区/厦门市/集美区/海沧区/区政府
 006 | 002 | 青春海 | 青春海/厦门市/集美区/同安区/未来海/海沧区/区政府

把查询结果输出为数组
select array(SELECT att.name FROM tree_test att) from dual;
 array
{厦门市,集美区,未来海,海沧区,区政府,同安区,青春海}

同一层级进行合并
select pid, string_agg(name, ',') from tree_test group by pid;
pid |        string_agg
-----+--------------------------
 001 | 南山区,龙华区,宝安区
 002 | 深圳湾,前海街道,粤海街道
 0   | 深圳市
 或者：
 select pid, array_to_string(array_agg(name),',') from tree_test group by pid;
  pid |     array_to_string
-----+--------------------------
 002 | 深圳湾,前海街道,粤海街道
 001 | 南山区,龙华区,宝安区
 0   | 深圳市
 
 同一层级进行合并,并且按name排序
 select pid, string_agg(name, ',' order by name desc) from tree_test group by pid;
pid |        string_agg
-----+--------------------------
 002 | 粤海街道,深圳湾,前海街道
 001 | 龙华区,宝安区,南山区
 0   | 深圳市