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 = 父节点idselect * from dudept start with deptCode='1001' connet by prior deptCode=pCode;第二种情况：start with 父节点id= ' 查询节点 ' connect by prior 子节点id = 父节点 idselect * from dudept start with deptCode='1001' connect by prior deptCode=pCode;prior 在父节点端（向上递归）第三种情况：start with 子节点id= ' 查询节点 ' connect by prior 父节点id = 子节点idselect * from dudept start with deptCode='1001' connect by prior pCode=deptCode;第四种情况：start with 父节点id= ' 查询节点 ' connect by prior 父节点id = 子节点idselect * 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 DETERMINISTICBEGINdeclare 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 DETERMINISTICbegin -- 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 DETERMINISTICbegin -- 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 DETERMINISTICbegin 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 DETERMINISTICbegin 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 DETERMINISTICbegin 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 Duselect getParentListId(4); select * from dudept where FIND_IN_SET(Id,getParentListId(4)); SQL: Recursive query in MySQLAmazon产品开发基础课程、 订单处理与业绩表现查询、 中国（绍兴）第二届跨境电商产业峰会暨2018年亚马逊运营破局思维分析、 惊爆！亚马逊ARA数据全公开，并在后台整合三方服务！、 良心干货！写好亚马逊listing关键词，才能提高转化率、 口述：嫂子新婚之夜错上了我的床、 纯干货：联系客服去差评技巧及模板汇总！、 2020年如何做海外红人营销？2020年如果找国外的网红营销？、
