问题[tree](dba)

我在 PostgreSQL 13 中有一个这样的菜单表：

-- Drop table

-- DROP TABLE public.menu_resource;

CREATE TABLE public.menu_resource (
    id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
    "name" varchar NOT NULL,
    res_type int4 NOT NULL,
    created_time int8 NOT NULL,
    updated_time int8 NOT NULL,
    remark varchar NULL,
    "path" varchar NOT NULL,
    parent_id int4 NOT NULL,
    component varchar NULL,
    sort int4 NOT NULL,
    name_zh varchar NOT NULL,
    tree_id_path varchar NULL,
    CONSTRAINT auth_resource_id_seq PRIMARY KEY (id)
);

parent_id菜单数据看起来像这样，带有一个用和标记的树结构id：

INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(13, 'job', 1, 1632389739938, 1632389739938, NULL, '/app/job', 1, NULL, 2, '求职管理', '5-1-13');
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(14, 'interview', 1, 1632389739938, 1632389739938, NULL, '/app/job/interview', 13, NULL, 2, '面试列表', '5-1-13-14');
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(15, 'menu', 1, 1632389739938, 1632389739938, NULL, '/privilege/menu', 2, './permission/menu', 0, '菜单列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(3, 'cruise', 1, 1632389739938, 1632389739938, NULL, '/app/cruise', 1, NULL, 2, 'Cruise', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(5, '系统菜单', 1, 1632389739938, 1632389739938, NULL, '/demo', 0, NULL, 0, '系统菜单', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(4, 'channel', 1, 1632389739938, 1632389739938, NULL, '/app/cruise/channel', 3, './apps/cruise/channel', 3, '频道', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(1, 'app', 1, 1632389739938, 1632389739938, NULL, '/app', 5, NULL, 1, '应用', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(2, 'privilege', 1, 1632389739938, 1632389739938, NULL, '/privilege', 5, NULL, 4, '权限管理', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(6, 'role', 1, 1632389739938, 1632389739938, NULL, '/privilege/role', 2, './permission/role', 5, '角色列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(7, 'article', 1, 1632389739938, 1632389739938, NULL, '/app/cruise/article', 3, './apps/cruise/article', 3, '文章', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(8, 'user', 1, 1632389739938, 1632389739938, NULL, '/privilege/user', 2, './permission/user', 5, '用户列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(9, 'welcome', 1, 1632389739938, 1632389739938, NULL, '/welcome', 5, './Welcome', 0, '欢迎', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(10, 'overview', 1, 1632389739938, 1632389739938, NULL, '/app/overview', 1, NULL, 2, '应用概览', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(11, 'product', 1, 1632389739938, 1632389739938, NULL, '/app/overview/product', 10, NULL, 2, '产品列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(12, 'list', 1, 1632389739938, 1632389739938, NULL, '/app/overview/list', 10, NULL, 2, '应用列表', NULL);

现在我想生成父子id的全路径，比如tree_id_pathfor job可能是这样的：5-1-13，interview可能是这样的5-1-13-14。我应该怎么做才能让它像这样工作？

基于Traversing tree-like data in a relational database using SQL question，我想知道在考虑物理含义的情况下，如何经常使用关系数据库上描述树状数据的方式？

我假设 RDBMS 除了常规的 SQL ANSI 或常见的可用特性之外没有特殊的特性来处理。

毫无疑问，我总是对 MySQL 和 PostgreSQL 以及最终的 SQLite 感兴趣。

有没有办法在 SQL 中遍历树数据？我知道connect by在 Oracle 中，但是在其他 SQL 实现中是否有另一种方法可以做到这一点？我问是因为使用connect by比编写循环或递归函数来为每个结果运行查询更容易。

由于有些人似乎对“树数据”这个短语感到困惑，我将进一步解释：我的意思是关于具有parent_id或类似字段的表，其中包含来自同一表中另一行的主键。

问题来自我在 Oracle 数据库中处理以这种方式存储的数据的经验，并且知道connect by其他 DBMS 中没有实现。如果要使用标准 SQL，则必须为每个想要上升的父表创建一个新的表别名。这很容易失控。