我发现更容易做到:

1)创建一个函数，检查一个项目是否在另一个项目的父层次结构中的任何地方。就像这样(我不会写函数，用WHILE DO):

is_related(id, parent_id);

在你的例子中

is_related(21, 19) == 1;
is_related(20, 19) == 1;
is_related(21, 18) == 0;

2)使用子选择，就像这样:

select ...
from table t
join table pt on pt.id in (select i.id from table i where is_related(t.id,i.id));

从博客管理分层数据在MySQL

表结构

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

查询:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

输出

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+

大多数用户都曾经在SQL数据库中处理过层次数据，毫无疑问，他们知道层次数据的管理不是关系数据库的目的。关系数据库的表不是分层的(像XML一样)，而只是一个平面列表。层次数据具有亲子关系，在关系数据库表中不能自然地表示这种关系。 阅读更多

更多细节请参考博客。

编辑:

select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where parent=@pv

输出:

category_id name    parent
19  category1   0
20  category2   19
21  category3   20
22  category4   21

参考:如何在Mysql中做递归SELECT查询?

使用BlueM/tree php类在mysql中创建一个自关系表的树。

Tree和Tree\Node是PHP类，用于处理使用父ID引用分层结构的数据。一个典型的例子是关系数据库中的一个表，其中每个记录的“父”字段引用另一个记录的主键。当然，Tree不能只使用来自数据库的数据，而是使用任何数据:您提供数据，Tree使用它，而不管数据来自何处以及如何处理。阅读更多

下面是一个使用BlueM/tree的例子:

<?php 
require '/path/to/vendor/autoload.php'; $db = new PDO(...); // Set up your database connection 
$stm = $db->query('SELECT id, parent, title FROM tablename ORDER BY title'); 
$records = $stm->fetchAll(PDO::FETCH_ASSOC); 
$tree = new BlueM\Tree($records); 
...

我向你提出了一个问题。这将给你递归类别与一个单一的查询:

SELECT id,NAME,'' AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 WHERE prent is NULL
UNION 
SELECT b.id,a.name,b.name AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id WHERE a.prent is NULL AND b.name IS NOT NULL 
UNION 
SELECT c.id,a.name,b.name AS subName,c.name AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id WHERE a.prent is NULL AND c.name IS NOT NULL 
UNION 
SELECT d.id,a.name,b.name AS subName,c.name AS subsubName,d.name AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id LEFT JOIN Table1 AS d ON d.prent=c.id WHERE a.prent is NULL AND d.name IS NOT NULL 
ORDER BY NAME,subName,subsubName,subsubsubName

这是一把小提琴。

这里没有提到的是，为每个项添加持久路径列，尽管它与第二种备选方案有点相似，但对于大型层次结构查询和简单的(插入、更新、删除)项来说不同且成本较低。

一些像:

id | name        | path
19 | category1   | /19
20 | category2   | /19/20
21 | category3   | /19/20/21
22 | category4   | /19/20/21/22

例子:

-- get children of category3:
SELECT * FROM my_table WHERE path LIKE '/19/20/21%'
-- Reparent an item:
UPDATE my_table SET path = REPLACE(path, '/19/20', '/15/16') WHERE path LIKE '/19/20/%'

优化路径长度和ORDER BY路径使用base36编码代替实际数值路径id

 // base10 => base36
 '1' => '1',
 '10' => 'A',
 '100' => '2S',
 '1000' => 'RS',
 '10000' => '7PS',
 '100000' => '255S',
 '1000000' => 'LFLS',
 '1000000000' => 'GJDGXS',
 '1000000000000' => 'CRE66I9S'

https://en.wikipedia.org/wiki/Base36

还通过对编码的id使用固定长度和填充来抑制斜杠'/'分隔符

详细优化说明如下: https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/

TODO

构建一个函数或过程，以分割检索一个项的祖先的路径

这是一个有点棘手的问题，检查一下它是否适合你

select a.id,if(a.parent = 0,@varw:=concat(a.id,','),@varw:=concat(a.id,',',@varw)) as list from (select * from recursivejoin order by if(parent=0,id,parent) asc) a left join recursivejoin b on (a.id = b.parent),(select @varw:='') as c  having list like '%19,%';

SQL小提琴链接http://www.sqlfiddle.com/#!2 / e3cdf / 2

用字段名和表名替换。