Mysql选择递归获取具有多个级别的所有子项 [英] Mysql select recursive get all child with multiple level

问题描述

我有一张桌子

 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`)
)

我填写这张表

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)

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)

当我选择一个文件夹 id = 4 ==> (5,7, 6)

When i select a folder id = 4 ==> (5,7, 6)

当我选择一个文件夹 id = 3 ==> (4, 5,6, 7)

When i select a folder id = 3 ==> (4, 5,6, 7)

如何通过一个查询做到这一点?

How to do that with one query?

谢谢

推荐答案

这是可行的

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;

在此处查看 SQL Fiddle:http://sqlfiddle.com/#!2/02b78/1

Look at SQL FIddle here:http://sqlfiddle.com/#!2/02b78/1

这篇关于Mysql选择递归获取具有多个级别的所有子项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！