mysql 递归函数

最新推荐文章于 2024-03-22 08:33:21 发布

转载最新推荐文章于 2024-03-22 08:33:21 发布 · 482 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/2335300/blog/741499

文章标签：

#数据库

2019独角兽企业重金招聘Python工程师标准>>>

业务描述：

选择部门时显示部门下面使用部门员工信息

部门数据：

DROP TABLE IF EXISTS `department`;

CREATE TABLE `department` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`Name` varchar(50) NOT NULL,

`ParentId` int(11) NOT NULL DEFAULT '-1',

PRIMARY KEY (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of department

-- ----------------------------

INSERT INTO `department` VALUES ('38', 'XXX总公司', '34');

INSERT INTO `department` VALUES ('39', '服务器端开发组', '38');

INSERT INTO `department` VALUES ('40', 'XXX分公司', '-1');

INSERT INTO `department` VALUES ('44', '财务部', '40');

INSERT INTO `department` VALUES ('46', 'IOS开发组', '38');

INSERT INTO `department` VALUES ('47', 'Android 开发组', '38');

部门用户：

DROP TABLE IF EXISTS `employee_basic`;

CREATE TABLE `employee_basic` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`Name` varchar(20) NOT NULL COMMENT '员工姓名',

`EmpNo` varchar(20) NOT NULL DEFAULT '暂无' COMMENT '员工编号',

`Email` varchar(50) NOT NULL COMMENT '员工邮箱',

`Position` varchar(20) NOT NULL COMMENT '职位',

`Sex` int(11) NOT NULL COMMENT '1-男，2-女',

`Birthday` date NOT NULL COMMENT '员工生日',

`EntryTime` date NOT NULL COMMENT '入职时间',

`DepartmentId` int(11) NOT NULL,

`School` varchar(30) DEFAULT '' COMMENT '毕业院校',

`Phone` varchar(15) DEFAULT '' COMMENT '电话',

`Address` varchar(30) DEFAULT '' COMMENT '地址',

`EmergencyMan` varchar(20) DEFAULT NULL,

`EmergencyPhone` varchar(15) DEFAULT NULL,

PRIMARY KEY (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of employee_basic

-- ----------------------------

INSERT INTO `employee_basic` VALUES ('18', '罗AAA', '暂无', 'yangtie@AAAA.com', 'CTO', '1', '2015-06-16', '2015-06-18', '39', '暨南大学', '1888888888', '十五', '罗AAA', '111');

INSERT INTO `employee_basic` VALUES ('21', '杨BBB', '暂无', 'yangtie@AAAA.com', '职员', '1', '2015-06-19', '2015-05-13', '39', '中山大学', '1888888888', '111111111111111111111111111111', '杨BBB', '1888888888');

INSERT INTO `employee_basic` VALUES ('22', '唐CCC', '暂无', 'yangtie@AAAA.com', '测试', '1', '2015-06-22', '2015-06-23', '44', '中山大学', '1888888888', '前山前山前山前山前山前真', '唐CCC', '1888888888');

INSERT INTO `employee_basic` VALUES ('23', '邓DDD', '暂无', '280591505@AAA.com', '开发经理', '1', '2015-04-20', '2015-05-01', '46', '桂林大学', '1888888888', '珠海市香洲青竹商务5205', '邓DDD', '1888888888');

INSERT INTO `employee_basic` VALUES ('24', '陈FFF', '暂无', '280591505@AAA.com', '开发', '2', '2015-06-20', '2014-01-01', '46', '珠海大学', '1888888888', '珠海', '陈FFF', '1888888888');邓DDD

INSERT INTO `employee_basic` VALUES ('28', '唐EEE', '暂无', '11@AAAA.com', '11', '1', '2015-06-08', '2015-06-01', '47', '111', '11', '111', '唐EEE', '111');

INSERT INTO `employee_basic` VALUES ('29', '李GGG', '暂无', '88@AAAA.com', '88', '2', '2015-07-01', '2015-07-01', '47', '888', '88', '88', '李GGG', '88');

递归查询函数：

ROP FUNCTION IF EXISTS queryChildrenIds;

CREATE FUNCTION `queryChildrenIds` (dId INT)

RETURNS VARCHAR(4000)

BEGIN

DECLARE sTemp VARCHAR(4000);

DECLARE sTempChd VARCHAR(4000);

SET sTemp = '$';

SET sTempChd = cast(dId as char);

WHILE sTempChd is not NULL DO

SET sTemp = CONCAT(sTemp,',',sTempChd);

SELECT group_concat(id) INTO sTempChd FROM department where FIND_IN_SET(ParentId,sTempChd)>0;

END WHILE;

return sTemp;

END;

测试查询结果：

1	`<br>SELECT` `queryChildrenIds(38)`

1	`SELECT` `e.*` `FROM` `employee_basic e` `INNER` `JOIN` `department d` `ON` `e.DepartmentId=d.Id` `where` `FIND_IN_SET(e.DepartmentId, queryChildrenIds(38));`