SQL: Recursive query in MySQL

DROP TABLE IF EXISTS `dudept`;
CREATE TABLE `dudept`  (
  `Id` int(11) NOT NULL AUTO_INCREMENT comment 'ID',
  `deptCode` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL comment'',
  `deptName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL comment'',
  `pCode` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL comment'',
  `ParentId`int(11) not null comment 'Parent ID',
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1000 ','liufu Group', null, 0);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1001 ','liufu jewelry (Beijing) Co., Ltd.,' 1000 ', 1);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1002 ','liufu jewelry (Shanghai) Co., Ltd.,' 1000 ', 1);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1003 ','Beijing information technology department','1001 ', 2);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1004 ','Beijing finance department','1001 ', 2);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1005 ','Beijing marketing promotion department','1001 ', 2);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1006 ','Beijing Information Technology Department 1','1003 ', 4);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1007 ','Beijing Information Technology Department 2','1003 ', 4);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1008 ','first group of Beijing Information Technology Department','1006 ', 7);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1009 ','group 2, Beijing Information Technology Department 1','1006 ', 7);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1010 ','group 1, Beijing Information Technology Department 2','1007 ', 8);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1011 ','group 2, Beijing Information Technology Department 2','1007 ', 8);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1012 ','Beijing marketing promotion department 1','1005 ', 6);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1013 ','shanghai information technology department','1002 ', 3);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1014 ','shanghai information technology research and development department','1013 ', 14);
Insert into 'dudept' ('deptcode ','deptname','pcode ','parentid') values ('1015 ','shanghai information technology R & D department 2','1013 ', 14);


--Oracle recursive query of geovindu Tu Ju Wen
/*
Prior at the child node side (recursion down)
The first case: start with child node id = query node connect by prior child node id = parent node ID
select * from dudept start with deptCode='1001' connet by prior deptCode=pCode;
The second case: start with parent node id ='query node'connect by prior child node id = parent node ID
select * from dudept start with deptCode='1001' connect by prior deptCode=pCode;
Prior at the parent node side (recursion up)
The third case: start with child node id ='query node'connect by prior parent node id = child node ID
select * from dudept start with deptCode='1001' connect by prior pCode=deptCode;
The fourth case: start with parent node id ='query node'connect by prior parent node id = child node ID
select * from dudept start with deptCode='1001' connect by prior pCode=deptCode;

*/

select * from dudept;
-- concat，concat_ ws，group_ Concat function
select FIND_IN_SET('b','a,b,c,d');

select * from dudept where FIND_IN_SET(deptCode,'1000,1001,1002');

select CONCAT('M','Y','S','Q','L') from dual; 

select group_concat(deptCode) from dudept;


select * from dudept where FIND_IN_SET(Id,'1,2,3');

#Department function
DELIMITER $$
DROP FUNCTION IF EXISTS `f_GetDepartmentName` $$
CREATE FUNCTION `f_GetDepartmentName` (did int) RETURNS varchar(100)
    READS SQL DATA
    DETERMINISTIC
BEGIN
declare str varchar(100);
return(select deptName from dudept where Id=did);
END $$
DELIMITER ;

select f_GetDepartmentName(1);


--MySQL user-defined functions to achieve recursive query
delimiter $$ 
drop function if exists `getChildList` $$ 
create function `getChildList` (duId varchar(50)) returns varchar(1000) 
    READS SQL DATA
    DETERMINISTIC
begin 
 -- declare duId varchar(10) default '1003';
 declare ids varchar(1000) default ''; 
 declare tempids varchar(1000);
 set duId='1003';
 set tempids = duId; 
 while tempids is not null do 
  set ids = CONCAT_WS(',',ids,tempids); 
  select GROUP_CONCAT(deptCode) into tempids from dudept where FIND_IN_SET(pCode,tempids)>0;  
 end while; 
 -- select ids;
 
 return ids; 
end;
$$ 
delimiter ; 

 select getChildList('1001');
 
select * from dudept where FIND_IN_SET(deptCode,getChildList('1001'));


#ID query
delimiter $$ 
drop function if exists `getChildListId` $$ 
create function `getChildListId` (duId int) returns varchar(1000) 
    READS SQL DATA
    DETERMINISTIC
begin 
 -- declare duId varchar(10) default '1003';
 declare ids varchar(1000) default ''; 
 declare tempids varchar(1000);
 -- set duId='1003';
 set tempids = duId; 
 while tempids is not null do 
  set ids = CONCAT_WS(',',ids,tempids); 
  select GROUP_CONCAT(Id) into tempids from dudept where FIND_IN_SET(ParentId,tempids)>0;  
 end while; 
 -- select ids; 
 return ids; 
end;
$$ 
delimiter ; 

 select getChildListId(4);
 
 select * from dudept where FIND_IN_SET(Id,getChildListId(4));
 
 

--Manually implement recursive query (upward recursion)
delimiter $$ 
drop function if exists `getParentList` $$ 
create function `getParentList` (duId varchar(10)) returns varchar(1000) 
    READS SQL DATA
    DETERMINISTIC
begin 
 declare ids varchar(1000); 
 declare tempid varchar(10); 
  
 set tempid = duId; 
 while tempid is not null do 
  set ids = CONCAT_WS(',',ids,tempid); 
  select pCode into tempid from dudept where deptCode=tempid; 
 end while; 
 return ids; 
end;
$$ 
delimiter ;

select getParentList('1001');
 
select * from dudept where FIND_IN_SET(deptCode,getParentList('1001'));

#ID query
delimiter $$ 
drop function if exists `getParentListId` $$ 
create function `getParentListId` (duId int) returns varchar(1000) 
    READS SQL DATA
    DETERMINISTIC
begin 
 declare ids varchar(100); 
 declare tempid varchar(100);  
 set ids='$';
 set tempid = CAST(duId as char);
 --  set ids = CONCAT_WS(',',ids,tempid); 
SET ids = CONCAT(ids,',',tempid);
SELECT ParentId INTO tempid FROM dudept WHERE Id = tempid;
 while tempid  <> 0 DO  
 --    set ids = CONCAT_WS(',',ids,tempid); 
  SET ids = CONCAT(ids,',',tempid);
SELECT ParentId INTO tempid FROM dudept WHERE Id = tempid; 
 end while; 
 return ids; 
end;
$$ 
delimiter ;

delimiter $$ 
drop function if exists `getParentListId` $$ 
create function `getParentListId` (duId varchar(10)) returns varchar(1000) 
    READS SQL DATA
    DETERMINISTIC
begin 
 declare ids varchar(1000); 
 declare tempid varchar(100);   
 set tempid = CAST(duId as char(5)); 
 while tempid <> 0 DO 
  set ids = CONCAT_WS(',',ids,tempid); 
  select ParentId into tempid from dudept where Id=tempid; 
 end while; 
 return ids; 
end;
$$ 
delimiter ;


-- Geovin Du
select getParentListId(4);
 
 select * from dudept where FIND_IN_SET(Id,getParentListId(4));