elementui树结构表格查询（后台语句）

• 开头
• 测试表
• 上下级都查询
• 最后

开头

公司用的SpringBoot+Vue+ElementUI的开发框架，数据库是PostgreSQL
遇到个问题，系统菜单用的el-table是树形数据

↑大概是这种
想实现后台检索
网上找了一下，有个“递归查询”

测试表

新建测试表

CREATE TABLE test_tree_table
(
id integer NOT NULL DEFAULT nextval(‘test_tree_table_id_seq’::regclass),
code character varying(50), – 编码
name character varying(100), – 名称
upcode character varying(50), – 上级编码
);

插入测试数据

INSERT INTO public.test_tree_table(code, name, upcode) VALUES (1, ‘01’, ‘一年级’, ‘0’);
INSERT INTO public.test_tree_table(code, name, upcode) VALUES (2, ‘0101’, ‘一班’, ‘01’);
INSERT INTO public.test_tree_table(code, name, upcode) VALUES (3, ‘0102’, ‘二班’, ‘01’);
INSERT INTO public.test_tree_table(code, name, upcode) VALUES (4, ‘0103’, ‘三班’, ‘01’);
INSERT INTO public.test_tree_table(code, name, upcode) VALUES (5, ‘02’, ‘二年级’, ‘0’);
INSERT INTO public.test_tree_table(code, name, upcode) VALUES (6, ‘0201’, ‘一班’, ‘02’);
INSERT INTO public.test_tree_table(code, name, upcode) VALUES (7, ‘0202’, ‘二班’, ‘02’);
INSERT INTO public.test_tree_table(code, name, upcode) VALUES (8, ‘020101’, ‘张三’, ‘0201’);

模糊查询"三"的下级内容

with recursive cteDOWN as(
select a.* from test_tree_table a where a.name like ‘%三%’
union all
select b.* from test_tree_table b join cteDOWN c on c.code = b.upcode
)select * from cteDOWN;

模糊查询"三"的上级内容

with recursive cteUP as(
select a.* from test_tree_table a where a.name like ‘%三%’
union all
select b.* from test_tree_table b join cteUP c on c.upcode = b.code
)select * from cteUP;

上下级都查询

// An highlighted block
with CC as (
	with AA as (
		with recursive cteUP as(
			select a.* from test_tree_table a where a.name LIKE '%三%'
			union all 
			select b.* from test_tree_table b join cteUP c on c.upcode = b.code 
		)select * from cteUP
	), BB as (
		with recursive cteDOWN as(
			select a.* from test_tree_table a where a.name LIKE '%三%'
			union all 
			select b.* from test_tree_table b join cteDOWN c on c.code = b.upcode
		)select * from cteDOWN
	)(select * from AA) UNION ALL (select * from BB)
)select DISTINCT * from CC;

最后

人生第一篇博客。大概就这样了，查询语句烂的很
小白一个，最讨厌和数据库打交道(；へ：)
写这个的目的是觉得以后可能用得到，记录一下(〃‘▽’〃)

参考地址: https://www.modb.pro/db/12389.