一个层次查询与自己的无环解决方案将提出。需要改进。
假设一棵树有或没有环（Oidipus）。

CREATE TABLE `person` (
  `ID` varchar(10) NOT NULL,
  `PARENT` varchar(10) NOT NULL,
  `TYPE` varchar(10) NOT NULL,
  `NAME` varchar(50) NOT NULL
)

字段TYPE和NAME不重要。连接通过字段PARENT中另一个人的ID实现。
1.查找父项：

WITH recursive Parents(ID, SUMID, TYPE, PARENT, LEVEL) AS (
  SELECT ID, Concat(ID,"Z","                  ...") AS SUMID, TYPE, PARENT, 0 AS LEVEL FROM `person` WHERE ID = '1000000005'
  UNION ALL
  SELECT m.ID, CONCAT(m.ID,"Z",SUMID) AS SUMID , m.TYPE, m.PARENT, LEVEL + 1 FROM `person` as m INNER JOIN Parents t on m.ID = t.PARENT
  WHERE LEVEL < 6
  AND INSTR ( SUMID, m.ID) < 1
)
SELECT * FROM Parents;

额外的列SUMID（连接的"数字" ID，分隔符="Z"）将用于检查NOCCYCLE（参见Oracle关键字）。（Oidipus在字段ID中只出现一次）。工作正常，但SUMID初始内容应编码为MAXLEVEL乘以10个"字符串"。
只起部分作用的方法：
1.查找所有儿童

WITH recursive Children(ID, SUMID, TYPE, PARENT, LEVEL) AS (
  SELECT ID, Concat(ID,"Z","                  ...") AS SUMID, TYPE, PARENT, 0 AS LEVEL FROM `person` WHERE ID = '1000000002'
  UNION ALL
  SELECT m.ID, CONCAT(m.ID,"Z",SUMID) AS SUMID , m.TYPE, m.PARENT, LEVEL + 1 FROM `person` as m INNER JOIN Children t on m.PARENT = t.ID
  WHERE LEVEL < 6
  AND INSTR ( SUMID, m.ID) < 1
)
SELECT * FROM Children;

当一个人有5个孩子和5 * 5 = 25个孙子等等，那么SUMID就不够长，而且所有孩子的脚本速度很慢，性能也很差，如何在简单的MySQL中实现"查找所有孩子"呢？
我试着实现一个树结构的层次查询。查询"查找孩子"是缓慢和低效的。我希望改进的建议。

INSERT INTO `person` (`ID`, `PARENT`, `TYPE`, `NAME`) VALUES
('1000000001', '1000000001', 'A', 'first'),
('1000000002', '1000000004', 'B', 'second'),
('1000000003', '1000000002', 'C', 'third'),
('1000000004', '1000000002', 'C', 'fourth'),
('1000000005', '1000000004', 'C', 'fifth'),
('1000000006', '1000000002', 'C', '6th'),
('1000000007', '1000000002', 'C', '7th'),
('1000000008', '1000000002', 'C', '8th'),
('1000000009', '1000000002', 'C', '9th'),
('1000000010', '1000000005', 'D', '10th'),
('1000000011', '1000000005', 'D', '11th'),
('1000000012', '1000000005', 'D', '12nd'),
('1000000013', '1000000005', 'D', '13rd');

结果：

MariaDB [devmysql]> WITH recursive Children(ID, SUMID, TYPE, PARENT, LEVEL) AS (
    ->   SELECT ID, Concat(ID,"Z","                  ") AS SUMID, TYPE, PARENT, 0 AS LEVEL FROM `person` WHERE ID = '1000000002'
    ->   UNION ALL
    ->   SELECT m.ID, CONCAT(m.ID,"Z",SUMID) AS SUMID , m.TYPE, m.PARENT, LEVEL + 1 FROM `person` as m INNER JOIN Children t on m.PARENT = t.ID
    ->   WHERE LEVEL < 6
    ->   AND INSTR ( SUMID, m.ID) < 1
    -> )
    -> SELECT * FROM Children;
+------------+-------------------------------+------+------------+-------+
| ID         | SUMID                         | TYPE | PARENT     | LEVEL |
+------------+-------------------------------+------+------------+-------+
| 1000000002 | 1000000002Z                   | B    | 1000000004 |     0 |
| 1000000003 | 1000000003Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000004 | 1000000004Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000006 | 1000000006Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000007 | 1000000007Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000008 | 1000000008Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000009 | 1000000009Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000005 | 1000000005Z1000000004Z1000000 | C    | 1000000004 |     2 |
| 1000000010 | 1000000010Z1000000005Z1000000 | D    | 1000000005 |     3 |
| 1000000011 | 1000000011Z1000000005Z1000000 | D    | 1000000005 |     3 |
| 1000000012 | 1000000012Z1000000005Z1000000 | D    | 1000000005 |     3 |
| 1000000013 | 1000000013Z1000000005Z1000000 | D    | 1000000005 |     3 |
+------------+-------------------------------+------+------------+-------+
12 rows in set, 11 warnings (0.004 sec)

Oracle的对等项如下所示（见下文）：
提示
在甲骨文中

• UNION ALL是强制性的，否则ORA-32040：递归WITH子句必须使用UNION ALL运算符
• 参数列表是强制性的，否则ORA-32039：递归WITH子句必须具有列别名列表
• 递归忽略否则系统错误
• LEVEL是ORACLE中的关键字，即使用LEV代替

WITH Children (ID, SUMID, LEVEL) 
AS
(
  SELECT
    m.ID,
    ',' || CAST(m.ID AS VARCHAR(120) || ','  AS SUMID,
    0 AS LEV
  FROM
    person  AS m
  WHERE
    ID = '1000000002'
  UNION ALL
  SELECT
    m.ID,
    t.SUMID || m.ID || ','  AS SUMID,
    LEV + 1 AS LEV
  FROM
    person AS m
  INNER JOIN
    Children AS t
      ON m.PARENT = t.ID
  WHERE
      t.LEV < 10
    AND INSTR(t.SUMID, CONCAT(',', m.ID, ',')) < 1
)
SELECT * FROM Children;

查询在600毫秒内传递了大约60000条记录。
查询已在两个方向上通过使用减号的oracle对应项进行了测试：

select ID
FROM person 
  START WITH ID = '1000000002'
  CONNECT BY NOCYCLE PRIOR ID = PARENT
MINUS
SELECT ID FROM (
WITH Children (ID, SUMID, LEVEL) 
AS
(
  SELECT
    m.ID,
    ',' || CAST(m.ID AS VARCHAR(120) || ','  AS SUMID,
    0 AS LEV
  FROM
    person  AS m
  WHERE
    ID = '1000000002'
  UNION ALL
  SELECT
    m.ID,
    t.SUMID || m.ID || ','  AS SUMID,
    LEV + 1 AS LEV
  FROM
    person AS m
  INNER JOIN
    Children AS t
      ON m.PARENT = t.ID
  WHERE
      t.LEV < 10
    AND INSTR(t.SUMID, CONCAT(',', m.ID, ',')) < 1
)
SELECT * FROM Children);

不提供任何记录。
查询在ORACLE中测试成功1秒。对我来说难以置信的快。
"Na ja，" Connect by可以自动提供更多功能：

• 按循环连接
• 按隔离叶连接
• 水平

[see：甲骨文公司[1]