一、使用语法
WITH RECURSIVE r AS (
SELECT * FROM [table] WHERE id = xxx
union ALL
SELECT [table].* FROM [table], r WHERE [table].id = r.parent_id
)
SELECT * FROM r ORDER BY id;
注:table为表名、parent_id为父类ID
二、使用示例
WITH RECURSIVE r AS (
SELECT * FROM dict_area_info_tab WHERE dict_area_info_tab_id = '4031'
union ALL
SELECT dict_area_info_tab.* FROM dict_area_info_tab, r WHERE dict_area_info_tab.dict_area_info_tab_id = r.v0008
)
SELECT dict_area_info_tab_id,v0007,v0008,v0009 FROM r ORDER BY v0007 desc
三、列转行使用函数
1、使用函数:string_agg (field_name, ',') 第一个field_name参数为字段名,第二个参数为连接符
2、使用函数:array_to_string(ARRAY_AGG(DISTINCT field_name),'-') field_name为字段名