检索顶级父 MySQL [英] Retrieve top-level parent MySQL

问题描述

我有下表:

  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)

I need to extract all the top level records (so the ones where the parent_id = 0). But only the records where the parent OR one of his children is searchable (searchable = 1)

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

So in this case, the output should be:

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

因为这些都是顶级记录，而且它自己或他的孩子之一(不管可搜索孩子有多深")都是可搜索的.

Because these are all top-level records and it self or one of his childeren (doesn't matter how 'deep' the searchable child is) is searchable.

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

I am working with MySQL. I am not really sure if it is possible to write this with just one query, but I assume it should be done with a piece of recursive code or a function.

** 注意:不知道这棵树有多深".

** Note: it is unknown how 'deep' the tree goes.

推荐答案

你将不得不使用存储过程来做到这一点.

You will have to use stored procedure to do it.

查找所有 searchable = 1 的行，将它们的 id 和 parent_id 存储在临时表中.然后进行自联接以将父项添加到此临时表中.重复直到不能添加更多行(显然最好确保树不是循环的).最后，您只有一个表，其中的行在树的某处具有可搜索的后代，因此只显示没有父级的行(在顶部).

Find all rows with searchable = 1, store their ids and parent_ids in a temp table. Then do self-joins to add parents to this temp table. Repeat until no more rows can be added (obviously better make sure tree is not cyclic). At the end you have a table only with rows that have a searchable descendant somewhere down the tree, so just show only rows with no parent (at the top).

假设你的表被称为my_table"，这个应该可以工作:

Assuming your table is called 'my_table' this one should work:

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

将等于SELECT id FROM my_table WHERE id_is_top_level_and_has_searchable_descendant

这篇关于检索顶级父 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！