一尘不染

MySQL选择递归获取所有子级

mysql

我有一张桌子

 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

选择idFolder = 1的文件夹时,应返回该文件夹和子子文件夹的所有子文件夹(2,3,4,5,6,7)

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

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

一个查询该怎么做?

谢谢


阅读 1611

收藏
2020-05-17

共1个答案

一尘不染

这是工作中的一个

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固件:http://sqlfiddle.com/#!2 /
02b78 / 1

2020-05-17