Mysql选择递归获取具有多个级别的所有子项
2021-11-20 00:00:00
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
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
相关文章