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));
