mysql - 检索顶级父级 MySQL

Question

我有下表:

  id  |  parent_id  |  searchable  |  value
--------------------------------------------
  1   |      0      |      0       |    a
  2   |      1      |      0       |    b
  3   |      2      |      1       |    c
  4   |      0      |      0       |    d
  5   |      4      |      1       |    e
  6   |      0      |      0       |    f
  7   |      6      |      0       |    g
  8   |      6      |      0       |    h
  9   |      0      |      1       |    i

我需要提取所有顶级记录(即 parent_id = 0 的记录)。但仅限父项或其子项之一可搜索的记录 (searchable = 1)

所以在这种情况下，输出应该是:

  id  |  parent_id  |  searchable  |  value
--------------------------------------------
  1   |     0       |      0       |    a
  4   |     0       |      0       |    d
  9   |     0       |      1       |    i

因为这些都是顶级记录，并且它本身或其子记录之一(无论可搜索的子记录有多“深”)都是可搜索的。

我正在使用 MySQL。我不太确定是否可以仅用一个查询来编写此代码，但我认为应该使用一段递归代码或函数来完成。

** 注意:不知道树有多“深”。

Answer 1

您必须使用存储过程来完成此操作。

查找 searchable = 1 的所有行，将它们的 id 和parent_ids 存储在临时表中。然后进行自连接以将父表添加到该临时表中。重复直到无法添加更多行(显然最好确保树不是循环的)。最后，您有一个表，其中仅包含在树中某处具有可搜索后代的行，因此仅显示没有父级的行(位于顶部)。

假设您的表名为“my_table”，这个表应该可以工作:

DELIMITER //
DROP PROCEDURE IF EXISTS top_level_parents//
CREATE PROCEDURE top_level_parents()
BEGIN
  DECLARE found INT(11) DEFAULT 1;
  DROP TABLE IF EXISTS parent_tree;
  CREATE TABLE parent_tree (id int(11) PRIMARY KEY, p_id int(11)) ENGINE=HEAP;
  INSERT INTO parent_tree
    SELECT id, parent_id FROM my_table
    WHERE searchable = 1;
  SET found = ROW_COUNT();
  WHILE found > 0 DO
    INSERT IGNORE INTO parent_tree
      SELECT p.id, p.parent_id FROM parent_tree c JOIN my_table p
      WHERE p.id = c.p_id;
    SET found = ROW_COUNT();
  END WHILE;
  SELECT id FROM parent_tree WHERE p_id = 0;
  DROP TABLE parent_tree;
END;//
DELIMITER ;

然后调用它:

CALL top_level_parents();

将等于从 my_table 中选择 id，其中 id_is_top_level_and_has_searchable_descendant