postgresql常用查询

1.对数据进行查重分组

-- 查询表格中乡镇和村的分组
select distinct town, string_agg(distinct village,',' ORDER BY village) as village from 
shuizha GROUP BY town ORDER BY town
-- 数据如下
--  town                village
-- 东亭街道	东街社区,映月社区,柏庄村,门楼村
-- 东北塘街道	严埭社区,大马巷村,正阳村,锡通社区,锦阳村
-- 东港镇	东升村,东南村,东湖村,东青河村,勤新村,张缪舍村,新巷村,港东村,黄土塘村
-- 云林街道	云龙社区,春雷村,春雷社区

select  string_agg(distinct '{"label":"' || town_name || '","value":"' || town_code || '"}','') as town,
 '{"river":[' || string_agg(distinct '{"label":"' || river_name || '","value":"' || river_code || '"}',',') || ']}' as river
 from town_river GROUP BY town_name order by town_name

-- {"label":"东亭街道","value":"001"}	{"river":[{"label":"东亭港","value":"002"},{"label":"九里河","value":"014"},{"label":"冷渎港","value":"004"},{"label":"北兴塘河","value":"005"},{"label":"张周桥港","value":"031"},{"label":"柏东港","value":"032"},{"label":"桐桥港","value":"016"}]}
-- {"label":"东北塘街道","value":"003"}	{"river":[{"label":"严埭港","value":"009"},{"label":"农新河","value":"033"},{"label":"北兴塘河","value":"005"},{"label":"北横河","value":"010"},{"label":"寺头港","value":"013"},{"label":"锡北运河","value":"003"}]}