CREATE TABLE `hr_dept` (
`deptId` BIGINT(200) NOT NULL,
`parent_id` BIGINT(200) DEFAULT NULL,
`dept_name` VARCHAR(128) DEFAULT NULL,
`dept_code` VARCHAR(24) DEFAULT NULL,
`dept_desc` VARCHAR(512) DEFAULT NULL,
`dept_office_address` VARCHAR(128) DEFAULT NULL,
`is_delete` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`deptId`)
)
INSERT INTO `hr_dept` VALUES ('1', '3', '行政部门', '100004', '管理公司事物', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('2', '3', '人事部门', '100003', '管理公司员工', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('3', '1', '测试部门', '100005', '前后台测试', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('4', '1', '移动应用部', '100006', '手机端开发', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('5', '1', '金融业务部门', '100007', '银行业务', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('6', '1', '战略规划部', '100001', '研发软件架构', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('7', '2', '应用开发部', '100002', 'PC端后台开发', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('8', '2', '咨询部', '100008', '接洽外部项目', '江苏无锡菱湖大道', '1');
INSERT INTO `hr_dept` VALUES ('9', '2', '华北交付中心', '100009', '调试软件项目', '江苏无锡菱湖大道', '1');
DELIMITER //
CREATE FUNCTION getChildrenOrgOfHr(orgid INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE oTemp VARCHAR(4000);
DECLARE oTempChild VARCHAR(4000);
SET oTemp = '$';
SET oTempChild = CAST(orgid AS CHAR);
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(deptId) INTO oTempChild FROM hr_dept WHERE FIND_IN_SET(parent_id,oTempChild) > 0;
END WHILE;
RETURN oTemp;
END //
DELIMITER ;
SELECT * FROM hr_dept WHERE FIND_IN_SET(deptId,getChildrenOrgOfHr(2));
02-21
636
12-19
1490