按 MySQL 的先前等效项连接

Connect By Prior Equivalent for MySQL

提问by Jake

All,

全部，

I have three fields in a table that define a parent child relationship present in a MySQL database version 5.0 . The table name is tb_Tree and it has the following data:

我在一个表中有三个字段，它们定义了 MySQL 数据库版本 5.0 中存在的父子关系。表名是 tb_Tree，它有以下数据：

Table Name: tb_Tree

Id | ParentId | Name
--------------------
1  | 0        | Fruits
2  | 0        | Vegetables
3  | 1        | Apple
4  | 1        | Orange
5  | 2        | Cabbage
6  | 2        | Eggplant

How do I write a Query to get all the children if a ParentId is specified. Note that the table entries given are just sample data and they can have many more rows. Oracle has a "CONNECT BY PRIOR" clause, but I didn't find anything similar for MySQL. Can anyone please advise?

如果指定了 ParentId，我如何编写查询来获取所有孩子。请注意，给出的表条目只是示例数据，它们可以包含更多行。Oracle 有一个“CONNECT BY PRIOR”子句，但我没有找到任何与 MySQL 类似的东西。任何人都可以请指教吗？

Thanks

谢谢

回答by mu is too short

MySQL doesn't support recursive queries so you have to do it the hard way:

MySQL 不支持递归查询，因此您必须以艰难的方式进行：

1. Select the rows where ParentID = Xwhere Xis your root.
2. Collect the Idvalues from (1).
3. Repeat (1) for each Idfrom (2).
4. Keep recursing by hand until you find all the leaf nodes.

1. 选择根ParentID = X所在的行X。
2. 收集Id(1) 中的值。
3. 对Id(2) 中的每个重复 (1 )。
4. 继续手动递归，直到找到所有叶节点。

If you know a maximum depth then you can join your table to itself (using LEFT OUTER JOINs) out to the maximum possible depth and then clean up the NULLs.

如果您知道最大深度，那么您可以将您的表与自身（使用 LEFT OUTER JOIN）连接到最大可能深度，然后清除 NULL。

You could also change your tree representation to nested sets.

您还可以将树表示更改为嵌套集。

回答by Yashpal Singla

You Can also look into this interesting blog, which demonstrate how can we get similar results in mysql

您还可以查看这个有趣的博客，它演示了我们如何在 mysql 中获得类似的结果

http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

回答by Mats Kindahl

This is an old thread, but since I got the question in another forum I thought I'd add it here. For this case, I created a stored procedure that is hard-coded to handle the specific case. This do, of course have some drawbacks since not all users can create stored procedures at will, but nevertheless.

这是一个旧线程，但由于我在另一个论坛中遇到了这个问题，我想我会在这里添加它。对于这种情况，我创建了一个硬编码的存储过程来处理特定情况。这样做当然有一些缺点，因为并非所有用户都可以随意创建存储过程，但仍然如此。

Consider the following table with nodes and children:

考虑带有节点和子节点的下表：

CREATE TABLE nodes (
       parent INT,
       child INT
);

INSERT INTO nodes VALUES
       ( 5,  2), ( 5, 3),
       (18, 11), (18, 7),
       (17,  9), (17, 8),
       (26, 13), (26, 1), (26,12),
       (15, 10), (15, 5),       
       (38, 15), (38, 17), (38, 6),
       (NULL, 38), (NULL, 26), (NULL, 18);

With this table, the following stored procedure will compute a result set consisting of all the decedents of the node provided:

使用此表，以下存储过程将计算由所提供节点的所有死者组成的结果集：

delimiter $$
CREATE PROCEDURE find_parts(seed INT)
BEGIN
  -- Temporary storage
  DROP TABLE IF EXISTS _result;
  CREATE TEMPORARY TABLE _result (node INT PRIMARY KEY);

  -- Seeding
  INSERT INTO _result VALUES (seed);

  -- Iteration
  DROP TABLE IF EXISTS _tmp;
  CREATE TEMPORARY TABLE _tmp LIKE _result;
  REPEAT
    TRUNCATE TABLE _tmp;
    INSERT INTO _tmp SELECT child AS node
      FROM _result JOIN nodes ON node = parent;

    INSERT IGNORE INTO _result SELECT node FROM _tmp;
  UNTIL ROW_COUNT() = 0
  END REPEAT;
  DROP TABLE _tmp;
  SELECT * FROM _result;
END $$
delimiter ;

回答by user5132533

The below selectlists all plants and their parentidup to 4-level (and of course you can extend the level):

下面select列出了所有植物及其parentid最多 4 个级别（当然您可以扩展级别）：

select id, name, parentid
,(select parentid from tb_tree where id=t.parentid) parentid2
,(select parentid from tb_tree where id=(select parentid from tb_tree where id=t.parentid)) parentid3
,(select parentid from tb_tree where id=(select parentid from tb_tree where id=(select parentid from tb_tree where id=t.parentid))) parentid4 
from tb_tree t

and then you can use this query to get the final result. for example, you can get all children of "Fruits" by the below sql:

然后您可以使用此查询来获得最终结果。例如，您可以通过以下 sql 获取“Fruits”的所有子项：

select id ,name from (
    select id, name, parentid
    ,(select parentid from tb_tree where id=t.parentid) parentid2
    ,(select parentid from tb_tree where id=(select parentid from tb_tree where id=t.parentid)) parentid3
    ,(select parentid from tb_tree where id=(select parentid from tb_tree where id=(select parentid from tb_tree where id=t.parentid))) parentid4 
    from tb_tree t) tt
where ifnull(parentid4,0)=1 or ifnull(parentid3,0)=1 or ifnull(parentid2,0)=1 or ifnull(parentid,0)=1

回答by SKDroid

Might be late post.

可能会迟到。

With MySQL8 you can achieve it with recursive clause. Here is the example.

使用 MySQL8，您可以使用递归子句实现它。这是示例。

 with recursive cte (id, name, parent_id) as (
  select     id,
             name,
             parent_id
  from       products
  where      parent_id = 19
  union all
  select     p.id,
             p.name,
             p.parent_id
  from       products p
  inner join cte
          on p.parent_id = cte.id
)
select * from cte;

For more help find another thread, Hope It will help someone.

如需更多帮助，请找到另一个线程，希望它会帮助某人。

回答by Stan Sokolov

The below stored procedure order a table that has rows with back reference to the previous one. Notice on the first step I copy rows into temp table - those rows match some condition. In my case those are rows that belong to the same linear (road that is used in GPS navigation). Business domain is not important. Just in my case I am sorting segments that belong to the same road

下面的存储过程对一个表进行排序，该表的行反向引用前一个行。注意第一步，我将行复制到临时表中 - 这些行匹配某些条件。在我的情况下，这些行属于同一线性（用于 GPS 导航的道路）。业务领域并不重要。就我而言，我正在对属于同一条道路的路段进行排序

DROP PROCEDURE IF EXISTS orderLocations; DELIMITER //

DROP PROCEDURE IF EXISTS orderLocations; 分隔符 //

CREATE PROCEDURE orderLocations(_full_linear_code VARCHAR(11)) BEGIN

创建程序 orderLocations(_full_linear_code VARCHAR(11)) BEGIN

DECLARE _code VARCHAR(11);
DECLARE _id INT(4);
DECLARE _count INT(4);
DECLARE _pos INT(4);

DROP TEMPORARY TABLE IF EXISTS temp_sort;

CREATE TEMPORARY TABLE temp_sort (
  id              INT(4) PRIMARY KEY,
  pos             INT(4),
  code            VARCHAR(11),
  prev_code       VARCHAR(11)
);

-- copy all records to sort into temp table - this way sorting would go all in memory
INSERT INTO temp_sort SELECT
                         id, -- this is primary key of original table
                         NULL, -- this is position that still to be calculated
                         full_tmc_code, -- this is a column that references sorted by
                         negative_offset -- this is a reference to the previous record (will be blank for the first)
                       FROM tmc_file_location
                       WHERE linear_full_tmc_code = _full_linear_code;

-- this is how many records we have to sort / update position
SELECT count(*)
FROM temp_sort
INTO _count;

-- first position index
SET _pos = 1;

-- pick first record that has no prior record
SELECT
  code,
  id
FROM temp_sort l
WHERE prev_code IS NULL
INTO _code, _id;

-- update position of the first record
UPDATE temp_sort
SET pos = _pos
WHERE id = _id;

-- all other go by chain link
WHILE (_pos < _count) DO
  SET _pos = _pos +1;

  SELECT
    code,
    id
  FROM temp_sort
  WHERE prev_code = _code
  INTO _code, _id;


  UPDATE temp_sort
  SET pos = _pos
  WHERE id = _id;

END WHILE;

-- join two tables and return position along with all other fields
SELECT
  t.pos,
  l.*
FROM tmc_file_location l, temp_sort t
WHERE t.id = l.id
ORDER BY t.pos;

END;