在MariaDB中使用简单的sql代码使用NOCYCLE (oracle)分层查询连接BY

Question

将给出一个具有自己的无循环解的分层查询。改进需要。

假设有或没有循环的树(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(连接的"numeric"id, separator="Z")将用于检查NOCCYCLE(参见Oracle关键字)。(Oidipus在field ID中只出现一次)。工作正常，但SUMID的初始内容应该编码为MAXLEVEL乘以10 "String"

只能部分工作:

2. 查找所有子节点

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的对应版本如下所示:
提示在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对应的MINUS对其连接进行了测试在两个方向:

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可以自动提供更多的功能:

• CONNECT_BY_ISCYCLE
• CONNECT_BY_ISLEAF
• 水平
  [see: oracle docs][1]https://docs.oracle.com/cd/B12037_01/server.101/b10759/pseudocolumns001.htm#i1009434]

Answer 1

只要不需要输出LEVEL和SUMID，就可以使用UNION而不是UNION ALL来防止循环被求值。

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

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')
;

Records: 13  Duplicates: 0  Warnings: 0

WITH
RECURSIVE
Children
AS
(
SELECT * FROM `person` WHERE ID = '1000000002'
UNION
SELECT m.* FROM `person` as m INNER JOIN Children t on m.PARENT = t.ID
)
SELECT * FROM Children 

6日7日8日9日10日核心