使用简单的SQL代码在MariaDB中实现带有NOCYCLE层次查询的CONNECT BY

k4emjkb1  于 2023-01-26  发布在  其他
关注(0)|答案(1)|浏览(132)

层次查询与自己的无环解决方案将提出。需要改进。
假设一棵树有或没有环(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)
ldxq2e6h

ldxq2e6h1#

只要不需要输出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

| 识别号|母体|类型|姓名|
| - ------|- ------|- ------|- ------|
| 十亿|十亿|乙|秒|
| 十亿|十亿|C级|三度|
| 十亿|十亿|C级|第四|
| 十亿|十亿|C级|第六次|
| 十亿|十亿|C级|第七次|
| 十亿|十亿|C级|第八次|
| 十亿|十亿|C级|第九次|
| 十亿|十亿|C级|第五|
| 十亿|十亿|D级|第十次|
| 十亿|十亿|D级|第十一届|
| 十亿|十亿|D级|第12次|
| 十亿|十亿|D级|第十三届|
fiddle

相关问题