一、问题场景
给定部门表，根据部门id查询它的所有子部门数据，并以json树形格式返回。类似的，有关所有具有父子关系的数据，且使用类似parent_id字段指明父行的这类问题。
二、解答
1.如果不以json树形格式返回，只返回一个总的数据集，直接使用递归CTE查询即可。
-- 表格定义
create table if not exists t_department(
id integer primary key,
department_name varchar(20) not null,
parent_id integer
);
-- 测试数据
insert into t_department values (1001, '一级', null);
insert into t_department values (2001, '二级001', 1001), (2002, '二级002', 1001), (2003, '二级003', 1001);
insert into t_department values (3001, '三级001', 2001), (3002, '三级002', 2001),
(3003, '三级003', 2002), (3004, '三级004', 2002),
(3005, '三级005', 2003), (3006, '三级006', 2003);
insert into t_department values (4001, '四级001', 3001), (4002, '四级002', 3003), (4003, '四级003', 3006);
insert into t_department values (5001, '五级001', 4002);
insert into t_department values (6001, '六级001', 5001);
-- 查询指定部门及其所有子部门数据
with recursive cte as(
select id, department_name, parent_id
from t_department
where id = 1001 -- 指定部门id
union all
select td.id, td.department_name, td.parent_id
from t_department td
join cte on td.parent_id = cte.id
)
select * from cte;
-- 返回结果
id |department_name|parent_id|
----+---------------+---------+
1001|一级 | |
2001|二级001 | 1001|
2002|二级002 | 1001|
2003|二级003 | 1001|
3001|三级001 | 2001|
3002|三级002 | 2001|
3003|三级003 | 2002|
3004|三级004 | 2002|
3005|三级005 | 2003|
3006|三级006 | 2003|
4001|四级001 | 3001|
4002|四级002 | 3003|
4003|四级003 | 3006|
5001|五级001 | 4002|
6001|六级001 | 5001|
2.如果以json树形格式返回，就麻烦多了，具体逻辑见注释。
注：需要了解递归CTE语句、pgsql的json数据类型及一些json处理函数。
WITH RECURSIVE cte AS ( -- 第一个递归cte，用来得到所有子节点的并集
SELECT 1 AS tree_level, id, department_name, parent_id, ARRAY(SELECT id FROM t_department WHERE parent_id = td.id) AS children
FROM t_department td WHERE id = 1001
UNION ALL
SELECT tree_level + 1, td.id, td.department_name, td.parent_id, ARRAY(SELECT id FROM t_department WHERE parent_id = td.id) AS children
FROM cte c
-- JOIN t_department td2 ON (td2.id = ANY(t.children))) -- 效果和下面一样，都是将cte的id和它的子id关联
JOIN t_department td ON (td.parent_id = c.id))
-- select * from cte order by id; -- debug 迭代取并集，得到所有子节点
, cte2 AS ( -- 第二个递归cte，用来构建json树形返回结果
SELECT tl.tree_level AS tree_level,
to_jsonb(ARRAY( -- 将最底层的id、部门、孩子默认为{}空的组合成array
SELECT a FROM ( -- 初始查询，实际上是查最底层的level，同时返回他们的parent_id
SELECT td.parent_id AS id, json_agg(jsonb_build_object('id', td.id, 'departname', td.department_name , 'children', '{}'::text[])) AS children
FROM cte c
JOIN t_department td ON (td.id = c.id)
WHERE c.tree_level = tl.tree_level -- tree_level = 最底层level
GROUP BY td.parent_id) a
)) AS list
FROM (SELECT MAX(tree_level)::int AS tree_level FROM cte) tl -- 从最底层开始，子查询仅用作设置初始条件值
UNION ALL
SELECT c2.tree_level - 1 as tree_level,
to_jsonb(ARRAY(
SELECT a FROM (
SELECT td.parent_id AS id, json_agg(jsonb_build_object('id', td.id, 'departname', td.department_name ,'children', coalesce(j->'children', '{}'))) AS children
FROM cte c
-- 第一个连接和初始条件一样，主要是加上每一层的叶子节点，它们的children为空，因此设置默认为{}
-- 第二个连接是关联到下一层查出的父id等于部门id的节点，主要是维护它们的children，不为空。
-- jsonb_array_elements(list) j ON (td.id = (j->>'id')::int) 这个list就是上一步查出的parent_id和children 这个就是原表id等于parent_id的
JOIN t_department td ON (td.id = c.id) LEFT JOIN jsonb_array_elements(list) j ON (td.id = (j->>'id')::int)
WHERE c.tree_level = c2.tree_level -- c2的层级逐渐上升
GROUP BY td.parent_id) a
)) AS list
FROM cte2 c2
WHERE tree_level >= 1 -- 递归结束条件
) -- select * from cte2; -- debug 查看所有层级返回的结果
SELECT list-> 0 -> 'children' -> 0 as nested_json_tree
FROM cte2
WHERE tree_level = 0;
-- 返回结果
nested_json_tree |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{"id": 1001, "children": [{"id": 2001, "children": [{"id": 3001, "children": [{"id": 4001, "children": {}, "departname": "四级001"}], "departname": "三级001"}, {"id": 3002, "children": {}, "departname": "三级002"}], "departname": "二级001"}, {"id": 2002, "children": [{"id": 3003, "children": [{"id": 4002, "children": [{"id": 5001, "children": [{"id": 6001, "children": {}, "departname": "六级001"}], "departname": "五级001"}], "departname": "四级002"}], "departname": "三级003"}, {"id": 3004, "children": {}, "departname": "三级004"}], "departname": "二级002"}, {"id": 2003, "children": [{"id": 3006, "children": [{"id": 4003, "children": {}, "departname": "四级003"}], "departname": "三级006"}, {"id": 3005, "children": {}, "departname": "三级005"}], "departname": "二级003"}], "departname": "一级"}
格式化显示
{
"id": 1001,
"children": [
{
"id": 2001,
"children": [
{
"id": 3001,
"children": [
{
"id": 4001,
"children": {},
"departname": "四级001"
}
],
"departname": "三级001"
},
{
"id": 3002,
"children": {},
"departname": "三级002"
}
],
"departname": "二级001"
},
{
"id": 2002,
"children": [
{
"id": 3003,
"children": [
{
"id": 4002,
"children": [
{
"id": 5001,
"children": [
{
"id": 6001,
"children": {},
"departname": "六级001"
}
],
"departname": "五级001"
}
],
"departname": "四级002"
}
],
"departname": "三级003"
},
{
"id": 3004,
"children": {},
"departname": "三级004"
}
],
"departname": "二级002"
},
{
"id": 2003,
"children": [
{
"id": 3006,
"children": [
{
"id": 4003,
"children": {},
"departname": "四级003"
}
],
"departname": "三级006"
},
{
"id": 3005,
"children": {},
"departname": "三级005"
}
],
"departname": "二级003"
}
],
"departname": "一级"
}
该实现参考了Stack Overflow上一位老哥的，但是还是存在问题，不能返回所有子部门数据，本文对其进行了改进，能够直接CV使用🤪。
参考链接：https://stackoverflow.com/questions/37646813/returning-adjacency-list-as-nested-json-in-postgres
当然，也可将构建json格式的逻辑放到java层（为避免循环调用SQL，应先把需要的数据全部查出来），这样SQL层和java层逻辑都比较简单、清晰，这里不再展开。
3.获取一个部门及其所有子部门的数据，仍有其他方式。
方式1：
可以设计一个字段，它的父id直接在前面
比如一级是1000，那么二级就是1000:2001、1000:2002，三级就是1000:2001:3001这种方式，然后查询的话，比如查询1000及其所有子节点，直接like '1000%'开头即可。
方式2：
如果前端一次查询只获取一个节点的下一级的所有子节点，而不是所有子节点，那么，可以直接查询一个节点的下一级所有子节点，这样逻辑更简单了。这种方式要求前端动态构建树形组件，用户点开一级菜单的展开，就去查询一次接口，创建子菜单。
4.如果传入多个id，要求查询这些id的所有子节点数据。
直接将=替换为in即可
with recursive cte as(
select id, department_name, parent_id
from t_department
where id in (2001, 2002) -- 指定一些部门id
union distinct
select td.id, td.department_name, td.parent_id
from t_department td
join cte on td.parent_id = cte.id
)
select * from cte;
5.如果传入多个id，要求分组查询这些id的所有子节点数据。
循环使用union all，java层进行拼接uion all，或者组数不多的话，直接放java层循环调用SQL也可。
with recursive cte as (select td.id, td.department_name, td.parent_id from t_department td where id in (5001, 5001, 6001)
union distinct select td.id, td.department_name, td.parent_id from t_department td join cte on td.parent_id = cte.id
) select cast('2001, 2002' AS varchar) AS department, array_agg(id) as departmentSubs from cte;
(with recursive cte(id) as (select td.id, td.department_name, td.parent_id from t_department td where id = 1001
union all select td2.id, td2.department_name, td2.parent_id from t_department td2 join cte on td2.parent_id = cte.id
) select cast('1001' AS varchar) AS department, array_agg(id) as departmentSubs from cte)
union all
(with recursive cte as (select td.id, td.department_name, td.parent_id from t_department td where id = 2001
union all select td2.id, td2.department_name, td2.parent_id from t_department td2 join cte on td2.parent_id = cte.id
) select cast('2001' AS varchar) AS department, array_agg(id) as departmentSubs from cte)
union all
(with recursive cte as (select td.id, td.department_name, td.parent_id from t_department td where id = 2002
union all select td2.id, td2.department_name, td2.parent_id from t_department td2 join cte on td2.parent_id = cte.id
) select cast('2002' AS varchar) AS department, array_agg(id) as departmentSubs from cte)
;
-- 返回结果
department|departmentsubs |
----------+----------------------------------------------------------------------------+
1001 |{1001,2001,2002,2003,3001,3003,3004,3005,3006,3002,4001,4002,4003,5001,6001}|
2001 |{2001,3001,3002,4001} |
2002 |{2002,3003,3004,4002,5001,6001} |
PS：以上都是实际工作中遇到的类似问题，虽然工作中第2点解答没用到，采用了第3点的方式2（更简单、更高效），但是这种纯SQL的实现方式还是值得学习的。
1833
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
