在我的sql中获取树形文件夹结构的父级和子级< 8，没有CTE [英] get parents and children of tree folder structure in my sql < 8 and no CTEs

问题描述

我有一个基于id，parent_id关系与其自身连接的文件夹表:

I have a folder table that joins to itself on an id, parent_id relationship:

CREATE TABLE folders (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  title nvarchar(255) NOT NULL,
  parent_id int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO folders(id, title, parent_id) VALUES(1, 'root', null);
INSERT INTO folders(id, title, parent_id) values(2, 'one', 1);
INSERT INTO folders(id, title, parent_id) values(3, 'target', 2);
INSERT INTO folders(id, title, parent_id) values(4, 'child one', 3);
INSERT INTO folders(id, title, parent_id) values(5, 'child two', 3);
INSERT INTO folders(id, title, parent_id) values(6, 'root 2', null);
INSERT INTO folders(id, title, parent_id) values(7, 'other child one', 6);
INSERT INTO folders(id, title, parent_id) values(8, 'other child two', 6);

我想要一个查询，以返回该记录的所有父级，直接返回到路线和任何子级.

I want a query that returns all the parents of that record, right back to the route and any children.

因此，如果我要求使用id=3文件夹，则会得到记录:1, 2, 3, 4, 5.我被困在如何养父母的路上.

So if I ask for folder with id=3, I get records: 1, 2, 3, 4, 5. I am stuck how to get the parents.

MYSQL的版本是5.7，并且没有立即进行升级的计划，因此遗憾的是CTE不是一个选择.

The version of MYSQL is 5.7 and there are no immediate plans to upgrade so sadly CTEs are not an option.

我已经创建了此 sql小提琴

推荐答案

在MySQL 8.0中，您可以使用

In MySQL 8.0, you can make use of the Recursive Common Table Expressions to adress this use case.

以下查询为您提供给定记录(包括记录本身)的父项:

The following query gives you the parents of a given record (including the record itself):

with recursive parent_cte (id, title, parent_id) as (
  select id, title, parent_id
  from folders
  where id = 3
  union all
  select  f.id, f.title, f.parent_id
  from folders f
  inner join parent_cte pc on f.id = pc.parent_id
)
select * from parent_cte;

| id  | title  | parent_id |
| --- | ------ | --------- |
| 3   | target | 2         |
| 2   | one    | 1         |
| 1   | root   |           |

这是一个稍有不同的查询，它返回给定记录的子树:

And here is a slightly different query, that returns the children tree of a given record:

with recursive children_cte (id, title, parent_id) as (
  select id, title, parent_id
  from folders
  where parent_id = 3
  union all
  select  f.id, f.title, f.parent_id
  from folders f
  inner join children_cte cc on f.parent_id = cc.id
)
select * from children_cte;

| id  | title     | parent_id |
| --- | --------- | --------- |
| 4   | child one | 3         |
| 5   | child two | 3         |

两个查询器可以组合如下:

Both queriers can be combined as follows:

with recursive parent_cte (id, title, parent_id) as (
  select id, title, parent_id
  from folders
  where id = 3
  union all
  select  f.id, f.title, f.parent_id
  from folders f
  inner join parent_cte pc on f.id = pc.parent_id
),
children_cte (id, title, parent_id) as (
  select id, title, parent_id
  from folders
  where parent_id = 3
  union all
  select  f.id, f.title, f.parent_id
  from folders f
  inner join children_cte cc on f.parent_id = cc.id
)
select * from parent_cte
union all select * from children_cte;

| id  | title     | parent_id |
| --- | --------- | --------- |
| 3   | target    | 2         |
| 2   | one       | 1         |
| 1   | root      |           |
| 4   | child one | 3         |
| 5   | child two | 3         |

DB Fiddle上的演示

Demo on DB Fiddle

这篇关于在我的sql中获取树形文件夹结构的父级和子级< 8，没有CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！