CREATE TABLE `folder` (
`idFolder` int(10) DEFAULT NULL,
`FolderName` varchar(20) DEFAULT NULL,
`idFolderParent` varchar(10) DEFAULT NULL
);
insert into `folder` (`idFolder`, `FolderName`, `idFolderParent`) values('1','3','8');
insert into `folder` (`idFolder`, `FolderName`, `idFolderParent`) values('2','8','9');
insert into `folder` (`idFolder`, `FolderName`, `idFolderParent`) values('3','9','11');
insert into `folder` (`idFolder`, `FolderName`, `idFolderParent`) values('4','9','13');
insert into `folder` (`idFolder`, `FolderName`, `idFolderParent`) values('5','13','16');
insert into `folder` (`idFolder`, `FolderName`, `idFolderParent`) values('6','13','18');
insert into `folder` (`idFolder`, `FolderName`, `idFolderParent`) values('7','18','20');
insert into `folder` (`idFolder`, `FolderName`, `idFolderParent`) values('8','18','25');
insert into `folder` (`idFolder`, `FolderName`, `idFolderParent`) values('9','25','32');
insert into `folder` (`idFolder`, `FolderName`, `idFolderParent`) values('10','32','36');
insert into `folder` (`idFolder`, `FolderName`, `idFolderParent`) values('11','32','45');
insert into `folder` (`idFolder`, `FolderName`, `idFolderParent`) values('12','25','47');
insert into `folder` (`idFolder`, `FolderName`, `idFolderParent`) values('13','47','49');
insert into `folder` (`idFolder`, `FolderName`, `idFolderParent`) values('14','47','50');
拿idFolderParent列的值遍歷FolderName列相等的記錄，如果找到匹配的記錄就一直遍歷下去，沒有就顯示當前的記錄，想得到以下結果：
FolderName idFolderParent
3 8
8 50
9 11
13 16
18 20
25 36
32 45
47 49
下面的函式不是我想要的結果
DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `getList`(rootId varchar(100)) RETURNS varchar(1000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempPar VARCHAR(1000);
SET sTemp = '';
SET sTempPar =rootId;
#回圈遞回
WHILE sTempPar is not null DO
SET sTemp = concat(sTemp,',',sTempPar);
SELECT group_concat(foldername) INTO sTempPar FROM folder where foldername<>idfolderparent and FIND_IN_SET(idfolderparent,sTempPar)>0;
END WHILE;
RETURN sTemp;
END$$
DELIMITER ;
select * from folder where FIND_IN_SET(foldername,getList('8'));
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/10275.html
標籤：MySQL
上一篇：mysql 8.0.19 安裝 An error occurred trying to add new users to the MySQL database
下一篇：mysql起不來