mysql向下递归找所有子级sql参数_MySQL选择递归获取所有子级

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)

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

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

How to do that with one query?

Thx

解决方案

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;