我有一张桌子
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)
一个查询该怎么做?
谢谢
这是工作中的一个
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