MySQL选择递归获取所有子级 [英] Mysql select recursive get all child with multiple level
本文介绍了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)
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?
Thx
推荐答案
这是正在工作的
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
Look at SQL FIddle here:http://sqlfiddle.com/#!2/02b78/1
这篇关于MySQL选择递归获取所有子级的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文