MySQL Mysql选择递归获取具有多个级别的所有子项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28363893/
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 select recursive get all child with multiple level
提问by Adrián E
i have a table
我有一张桌子
CREATE TABLE IF NOT EXISTS `Folder` (
`idFolder` INT(11) NOT NULL AUTO_INCREMENT,
`FolderName` VARCHAR(150) NOT NULL,
`idFolderParent` INT(11) NULL,
PRIMARY KEY (`idFolder`),
CONSTRAINT `fk_1`
FOREIGN KEY (`idFolderParent`)
REFERENCES `Folder` (`idFolder`)
)
i fill this table by
我填写这张表
idFolder , FolderName , idFolderParent
1 ADoc Null
2 ADoc1 1
3 ADoc2 2
4 ADoc3 3
5 ADoc4 4
6 ADoc5 5
7 ADoc6 4
when select a folder with idFolder=1, it should be return all child for this folder and subchild folder(2, 3, 4, 5 ,6 ,7)
当使用IDFolder = 1选择一个文件夹时，它应该返回此文件夹和子填充文件夹（2,3,4,5,6,7）的所有子项
When i select a folder id = 4 ==> (5,7, 6)
当我选择一个文件夹 id = 4 ==> (5,7, 6)
When i select a folder id = 3 ==> (4, 5,6, 7)
当我选择一个文件夹 id = 3 ==> (4, 5,6, 7)
How to do that with one query?
如何通过一个查询做到这一点？
Thx
谢谢
采纳答案by Dheerendra Kulkarni
Here is the working one
这是工作的
SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder WHERE idFolderParent IN (@pv)) AS lv FROM Folder
JOIN
(SELECT @pv:=1)tmp
WHERE idFolderParent IN (@pv)) a;
Look at SQL FIddle here:http://sqlfiddle.com/#!2/02b78/1
在此处查看 SQL Fiddle：http://sqlfiddle.com/#!2/02b78/1
回答by Adrián E
None of the previous solutions worked for me. Both only work if the parents are saved into the database in a certain order.
以前的解决方案都不适合我。两者都只有在父母以特定顺序保存到数据库中时才有效。
I have to admit I do not fully understand the way the query works but could find a way that works for me (at least better than the other answers).
我不得不承认我并不完全理解查询的工作方式，但可以找到一种对我有用的方法（至少比其他答案更好）。
The data with which the first and second queries do not work is:
第一个和第二个查询不起作用的数据是：
idFolder , FolderName , idFolderParent
1 ADoc Null
2 ADoc1 7
3 ADoc2 2
4 ADoc3 3
5 ADoc4 Null
6 ADoc5 5
7 ADoc6 5
If you use the first and second queries in this dataset, for the id 5 you only get as a result '6,7'. But if you use my query you get: '6,7,2,3,4' which is the expected result.
如果您在此数据集中使用第一个和第二个查询，对于 id 5，您只会得到结果“6,7”。但是如果你使用我的查询，你会得到：'6,7,2,3,4' 这是预期的结果。
My version:
我的版本：
SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder
WHERE FIND_IN_SET(idFolderParent, @pv)) AS lv FROM Folder
JOIN
(SELECT @pv:=5) tmp
) a;
Hope it helps someone. I cannot comment nor downvote the other answers because of lack of reputation :(
希望它可以帮助某人。由于缺乏声誉，我无法评论或贬低其他答案:(
回答by asceta
Be aware the MySQL treats the
请注意 MySQL 对待
idFolderParent IN ('1, 2')
as a single value, so it is equal to:
作为单个值，所以它等于：
idFolderParent IN ('1')
so in order to operate on the list, you need to:
所以为了对列表进行操作，你需要：
SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder WHERE
FIND_IN_SET(idFolderParent, @pv)) AS lv FROM Folder
JOIN (SELECT @pv:=1)tmp
WHERE idFolderParent IN (@pv)) a;
(the FIND_IN_SET)
（FIND_IN_SET）
回答by O?uz Can Sertel
select idFolder, FolderName, idFolderParent
from (select * from Folder order by idFolderParent, idFolder) folders_sorted,
(select @pv := 1) initialisation
where find_in_set(idFolderParent, @pv) > 0
and @pv := concat(@pv, ',', idFolder)
@pv := 1 is your current Folder id.. I think this is much better solution.
@pv := 1 是您当前的文件夹 ID .. 我认为这是更好的解决方案。
Dheerendra Kulkarni's answer which is below gives me this error for different collations. I hope this will helps someone.
下面的 Dheerendra Kulkarni 的回答为我提供了不同排序规则的错误。我希望这会帮助某人。
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
操作 '=' 的排序规则 (utf8_general_ci,IMPLICIT) 和 (utf8_unicode_ci,IMPLICIT) 的非法混合
SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder WHERE idFolderParent IN (@pv)) AS lv FROM Folder
JOIN
(SELECT @pv:=1)tmp
WHERE idFolderParent IN (@pv)) a;
回答by Sachin Sarola
May be it works if you don't want to main id in result remove SELECT 4 Level UNION from query
如果您不想在结果中删除主 ID 从查询中删除 SELECT 4 Level UNION 可能会起作用
SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
SELECT 4 Level
UNION
SELECT @Ids := (SELECT GROUP_CONCAT(idFolderSEPARATOR ',') FROM folderWHERE FIND_IN_SET(idFolderParent, @Ids)) Level
FROM folderJOIN (SELECT @Ids := 4) temp1
) temp2
SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM ( SELECT 4 Level UNION SELECT @Ids := (SELECT GROUP_CONCAT( idFolderSEPARATOR ',') FROM folderWHERE FIND_IN_SET( idFolderParent, @Ids)) Level FROM folderJOIN (SELECT @Ids := 4) temp1 ) 温度 2
Look at SQL FIddle here : http://sqlfiddle.com/#!9/a2b4b3/312
在此处查看 SQL Fiddle：http://sqlfiddle.com/#!9/a2b4b3/312
回答by Manoj Rana
to get all level child of a particular parent you can use this below query:
要获取特定父级的所有级别子级，您可以使用以下查询：
select idFolder from (select * from Folder order by idFolderParent, idFolder) Folder, (select @pv := '1') initialisation where find_in_set(idFolderParent, @pv) > 0 and @pv := concat(@pv, ',', idFolder )
