MySQL MySQL递归从父级获取所有子级
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41913460/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Recursive get all child from parent
提问by Bakti Wijaya
i have this case using recursive query on Mysql to find lv 2 and lv3 child on one table...
database structure i'm using:
我在这种情况下使用 Mysql 上的递归查询在一张表上查找 lv 2 和 lv3 子级...
我正在使用的数据库结构：
id name parent
1 A 0
2 B 0
3 C 0
4 D 1
5 E 1
6 F 2
7 G 2
8 H 3
9 I 3
10 J 4
11 K 4
The result i was expecting, when filtering the data, where id=1, it will generate the result i'm expecting.
我期待的结果，当过滤数据时，id=1，它会产生我期待的结果。
id name parent
4 D 1
5 E 1
10 J 4
11 K 4
or this is the illustration.
或者这就是插图。
i've been looking everywhere, and reading this http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/, but i didn't find the result i was looking for..
any help would be appreciated, thanks
我一直在到处寻找，并阅读这个http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/，但我没有找到我正在寻找的结果......
任何帮助将不胜感激， 谢谢
采纳答案by Ollaw
SELECT *
FROM TABLENAME
WHERE PARENT = 1
UNION
SELECT *
FROM TABLENAME
WHERE PARENT IN
(SELECT ID FROM TABLENAME WHERE PARENT = 1)
回答by Manoj Rana
if you want to get all level child of a particular parent then you should try this
如果你想得到一个特定父母的所有级别的孩子，那么你应该试试这个
select id,
name,
parent
from (select * from tablename
order by parent, id) tablename,
(select @pv := '1') initialisation
where find_in_set(parent, @pv) > 0
and @pv := concat(@pv, ',', id)
回答by Meet Prajapati
Thanks @Manoj Rana your solutionis really helped me a lot. But I want to use this solution in Hibernate createNativeQuery();function. Because of :=operator I can't able to use. So I prepared new stored procedure using your solution and use it in my code.
谢谢@Manoj Rana， 您的解决方案对我帮助很大。但是我想在 Hibernate createNativeQuery(); 中使用这个解决方案；功能。由于:=运算符，我无法使用。所以我使用您的解决方案准备了新的存储过程并在我的代码中使用它。
You can find The stored procedure which I have created in this link
您可以在此链接中找到我创建的存储过程
回答by pradeep
Try this, Much faster
试试这个，快得多
SELECT * FROM table AS T1 INNER JOIN (SELECT id FROM table WHERE parent = 1) AS T2 ON T2.id = T1.parent OR T1.parent = 1 GROUP BY T1.id
回答by ersen
The answer you are looking for can be this; https://github.com/ersengultepe/mysql_hierarchy_recursive_procedure/
您正在寻找的答案可能是这样的； https://github.com/ersengultepe/mysql_hierarchy_recursive_procedure/
DROP PROCEDURE IF EXISTS store_procedure_name;
CREATE PROCEDURE `store_procedure_name`(IN cat_id INT)
BEGIN
declare loopId Int;
SET max_sp_recursion_depth = 255;
-- If the value of the category that comes as a parameter is not in the table as parent_id, no further action is required
IF(select count(id) from category_table where parent_id=cat_id) > 0 THEN
-- create temporary table
CREATE TEMPORARY TABLE IF NOT EXISTS temp_category_table (
`id` smallint(5) unsigned,
`status` tinyint(3)
) ENGINE=InnoDB ;
-- First, save the corresponding value in the temporary table.
INSERT INTO temp_category_table
(id, status)
VALUES (cat_id, 0);
-- continue loop as long as the appropriate record exists in the temporary table
WHILE (select count(id) from temp_category_table where status=0) > 0 DO
-- in this section, a record with a status of 0 will be extracted from the temporary table and assigned to the variable loopId
set loopId = (select id from temp_category_table where status=0 limit 1);
INSERT INTO temp_category_table
(id, status)
(select id, 0 from category_table where parent_id=loopId);
update temp_category_table set status=1 where id=loopId;
CALL store_procedure_name((select id from temp_category_table where status=0 limit 1));
END WHILE;
(select DISTINCT(id) from temp_category_table order by id );
END IF;
END;
回答by Sam
Try this one , much simple and easy to understand.
试试这个，简单易懂。
SET @pv = 1;
select * from tablename
where FIND_IN_SET(parentrecordID,@pv) and @pv:= concat(@pv, ',',id);
回答by Hala Atef
I tried this one
我试过这个
select id from (select * from roles order by parent_role, id) roles,(select @pv := '1') initialisation
where find_in_set(parent_role, @pv) > 0
and @pv := concat(@pv, ',', id)
but it works for depth 2 only, i need to make it work with more depth since i have 8 levels
但它仅适用于深度 2，因为我有 8 个级别，所以我需要让它更深地工作
