mariadb 在mysql中获取包含所有父节点的任何节点的父/子关系的完整树

kzipqqlq 于 2022-11-08 发布在 Mysql

关注(0)|答案(1)|浏览(140)

示例数据：

+----+-------+----------+
| org_id | Name  | ParentID |
+----+-------+----------+
|  1 | Org1  | 2        |
|  2 | Org2  | NULL     |
|  3 | Org3  | 5        |
|  5 | Org5  | 1        |
| 14 | Org14 | 1        |
+----+-------+----------+

如果我以org_id为1（Org1）的用户身份登录，我希望检索该用户的完整树。
我有以下递归查询：

WITH RECURSIVE cte (org_id, name, parent_id) AS (
     SELECT org_id, name, parent_id
     FROM organization
     WHERE org_id = 1
     UNION ALL
     SELECT t1.org_id, t1.name, t1.parent_id
     FROM organization t1
     INNER JOIN cte t2 ON t1.parent_id = t2.org_id
)
SELECT * FROM cte;

然而，这个查询只给出了当前id的子节点（在这个例子中是Org1），我怎样才能把所有的父节点也包括在结果集中，这样我就可以准确地重建整个树了？
编辑：我使用的是MariaDB版本10.4.10
编辑：我尝试了下面的答案中的查询，我得到了一个语法错误：

mariadb

来源：https://stackoverflow.com/questions/60018635/get-full-tree-of-parent-child-relationships-in-mysql-of-any-node-in-the-tree-wit

1条答案

按热度按时间

azpvetkf1#

你有一个CTE可以得到孩子。为什么不用另一个CTE去相反的方向，得到父母：
MySQL数据库：

(WITH RECURSIVE cte (id, name, parent_id) AS (
     SELECT id, name, parent_id
     FROM organization
     WHERE id = 1
     UNION  
     SELECT t1.id, t1.name, t1.parent_id
     FROM organization t1
       INNER JOIN cte t2 ON t1.parent_id = t2.id 
)
SELECT * FROM cte)
UNION
(WITH RECURSIVE cte (id, name, parent_id) AS (
     SELECT id, name, parent_id
     FROM organization
     WHERE id = 1
     UNION 
     SELECT t1.id, t1.name, t1.parent_id
     FROM organization t1
       INNER JOIN cte t2 ON t2.parent_id = t1.id 
)
SELECT * FROM cte)

以及一个可在MySQL和MariaDB中使用的版本：
MySQL/玛丽亚数据库：

WITH RECURSIVE cte (id, name, parent_id, dir) AS (
     SELECT id, name, parent_id, cast(null as char(10)) as dir
     FROM organization
     WHERE id = 1
     UNION  
     SELECT t1.id, t1.name, t1.parent_id, ifnull(t2.dir, 'down')
     FROM organization t1
       INNER JOIN cte t2 ON t1.parent_id = t2.id and ifnull(t2.dir, 'down')='down'
     UNION
     SELECT t1.id, t1.name, t1.parent_id, ifnull(t2.dir, 'up')
     FROM organization t1
       INNER JOIN cte t2 ON t2.parent_id = t1.id and ifnull(t2.dir, 'up')='up'
)
SELECT id, name, parent_id FROM cte;

请参阅db-fiddle和dbfiddle

赞(0）回复(0）举报 2022-11-08

我来回答

mariadb 在mysql中获取包含所有父节点的任何节点的父/子关系的完整树

1条答案

相关问题

热门标签

最新问答