PostgreSQL递归查询

在软件开发中，我们经常会遇到树型结构数据，比如行政区划，系统菜单，组织机构等信息。这样的数据结构我们有时候在查询中希望得到某个节点的全部父节点，或者某个节点的全部子节点等操作，在PostgreSQL中，可以使用其递归查询将树型数据结构转换为平面数据结构，通过简单的数据视图的查询即可得到相关的数据。

下面，我们通过一个示例来说明递归查询的编写方法。

示例：树型菜单转换为平面数据。

1.树型结构数据

Name

Code

Data Type

Length

Precision

Primary

Foreign Key

Mandatory

功能id

func_id

VARCHAR(64)

64

TRUE

FALSE

TRUE

父功能id

parent_id

VARCHAR(64)

64

FALSE

FALSE

FALSE

功能名称

func_name

VARCHAR(32)

32

FALSE

FALSE

TRUE

执行动作

action

VARCHAR(512)

512

FALSE

FALSE

FALSE

顺序号

serial_no

INT2

2

FALSE

FALSE

FALSE

图标

icon

VARCHAR(512)

512

FALSE

FALSE

FALSE

系统标识

system_flag

VARCHAR(64)

64

FALSE

FALSE

FALSE

SQL脚本：

create table c_sys_func (   func_id              VARCHAR(64)          not null,   parent_id            VARCHAR(64)          null,   func_name            VARCHAR(32)          not null,   action               VARCHAR(512)         null,   serial_no            INT2                 null,   icon                 VARCHAR(512)         null,   system_flag          VARCHAR(64)          null,   constraint PK_C_SYS_FUNC primary key (func_id));

我们约定，parent_id 为空表示最上层的节点，接下来，我们插入示例数据如下：

delete from backstage.c_sys_func where system_flag = 'BACKSTAGE';--租户中心INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('zhzx', null, '租户中心', null, 0, 'Avatar', 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('cyzhgl', 'zhzx', '餐饮组织管理', '/tenant_center/org_restaurant.html', 5, null, 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('gysgl', 'zhzx', '供应商管理', '/tenant_center/org_supplier.html', 10, null, 'BACKSTAGE');--标准中心INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('bzzx', null, '标准中心', null, 50, 'DocumentChecked', 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('bzspgl', 'bzzx', '标准商品管理', null, 55, 'Goods', 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('splm', 'bzspgl', '商品类目', '/standard_center/goods_category.html', 60, null, 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('spgl', 'bzspgl', '标准商品', '/standard_center/standard_goods.html', 65, null, 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('spzdjgl', 'bzspgl', '商品指导价管理', '/standard_center/standard_goods_price.html', 70, null, 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('wsjcbz', 'bzzx', '卫生检查标准', '/standard_center/standard_hygiene.html', 75, null, 'BACKSTAGE');--基础中心INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('jczx', null, '基础中心', null, 100, 'Coin', 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('xzqh', 'jczx', '行政区划', '/base_canter/admin_div.html', 105, null, 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('sjzd', 'jczx', '数据字典', '/base_canter/data_dict.html', 110, null, 'BACKSTAGE');--用户中心INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('yhzx', null, '用户中心', null, 150, 'User', 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('jggl', 'yhzx', '机构管理', '/user_center/institution.html', 155, null, 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('yhgl', 'yhzx', '用户管理', '/user_center/user.html', 160, null, 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('mrmm', 'yhzx', '默认密码', '/user_center/default_pwd.html', 165, null, 'BACKSTAGE');--权限中心INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('qxzx', null, '权限中心', null, 200, 'Checked', 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('yygl', 'qxzx', '应用管理', '/right_center/app_manage.html', 205, null, 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('jsgl', 'qxzx', '角色管理', '/right_center/role_manage.html', 210, null, 'BACKSTAGE');INSERT INTO backstage.c_sys_func(func_id, parent_id, func_name, "action", serial_no, icon, system_flag)VALUES('qxgl', 'qxzx', '权限管理', '/right_center/right_manage.html', 215, null, 'BACKSTAGE');

查询的数据结果如下图：

2.递归查询

递归查询的思路：首先查询最上层的节点数据，然后与自身关联，关联的条件为上层节点的id与自身数据的parent_id相等，在关联查询中，将数据连接成一个新的字段，该字段的类型可以为数组，也可以是字符串，但为了在后面的开发中使用起来更加方便，建议新的字段类型为数组比较合适。

首先，我们编写查询最上层的节点数据SQL：

select func_id, parent_id, func_name, action, serial_no, icon, system_flag,         1 as depth,         array[func_id::varchar] as path,        array[func_name::varchar] as full_func_name from backstage.c_sys_func c where parent_id is null

在上面的查询中，func_id, parent_id, func_name, action, serial_no, icon, system_flag字段为表中的字段，depth是计算字段，设置为1表示数据的层级是1，path 和 full_func_name 均为数组类型，其值的对应的 id 和 名称，path 表示节点的路径，full_func_name 表示节点的全名称路径。查询结果如下：

然后，在此基础上，我们使用递归查询将数据表与上面的根节点进行关联，SQL语句如下：

with recursive cte as( -- 先查询root节点  select func_id, parent_id, func_name, action, serial_no, icon, system_flag,         1 as depth,         array[func_id::varchar] as path,        array[func_name::varchar] as full_func_name from backstage.c_sys_func c where parent_id is null union all -- 通过cte递归查询root节点的直接子节点  select t.func_id, t.parent_id, t.func_name, t.action, t.serial_no, t.icon, t.system_flag,         cte.depth + 1 as depth,         array_append(cte.path, t.func_id::varchar) as path,        array_append(cte.full_func_name, t.func_name::varchar) as full_func_name  from cte join backstage.c_sys_func as t on t.parent_id = cte.func_id)select * from cte;

在SQL语句中，三个计算字段的代码是最关键的，分别如下：

cte.depth + 1 as depth, array_append(cte.path, t.func_id::varchar) as path,array_append(cte.full_func_name, t.func_name::varchar) as full_func_name 

表示层级的 depth 使用了 + 1的行为，表示路径的两个字段分别将相应的字段值追加到数组中，在此处，要注意类型的转换，使用了 ::varchar 来将数据转换为无长度标识的变长字符串类型，类型转换不正确有可能会出错。

查询结果如下：