SQL: Recursive query in MySQL

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199

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', '六福集团', NULL,0); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1001', '六福珠宝(北京)公司', '1000',1); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1002', '六福珠宝(上海)公司', '1000',1); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1003', '北京资讯科技部', '1001',2); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1004', '北京财务部', '1001',2); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1005', '北京营销推广部', '1001',2); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1006', '北京资讯科技一部', '1003',4); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1007', '北京资讯科技二部', '1003',4); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1008', '北京资讯科技一部一小组', '1006',7); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1009', '北京资讯科技一部二小组', '1006',7); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1010', '北京资讯科技二部一小组', '1007',8); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1011', '北京资讯科技二部二小组', '1007',8); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1012', '北京营销推广一部', '1005',6); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1013', '上海资讯科技部', '1002',3); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1014', '上海资讯科技研发一部', '1013',14); INSERT INTO `dudept`(`deptCode`, `deptName`, `pCode`,`ParentId`) VALUES ('1015', '上海资讯科技研发二部', '1013',14);     -- Oracle 递归查询 geovindu Geovin Du 涂聚文 /* prior 在子节点端（向下递归） 第一种情况：start with 子节点id = ' 查询节点 ' connect by prior 子节点id = 父节点id select * from dudept start with deptCode='1001' connet by prior deptCode=pCode; 第二种情况：start with 父节点id= ' 查询节点 '  connect by prior 子节点id = 父节点 id select * from dudept start with deptCode='1001' connect by prior deptCode=pCode; prior 在父节点端（向上递归） 第三种情况：start with 子节点id= ' 查询节点 ' connect by prior 父节点id = 子节点id select * from dudept start with deptCode='1001' connect by prior pCode=deptCode; 第四种情况：start with 父节点id= ' 查询节点 ' connect by prior 父节点id = 子节点id select * from dudept start with deptCode='1001' connect by prior pCode=deptCode;   */   select * from dudept; -- concat，concat_ws，group_concat 函数 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');   #部门函数 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 自定义函数，实现递归查询 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 查询 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));         -- 手动实现递归查询（向上递归） 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 查询 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));