查询部门（包含子部门）员工

部门表

CREATE TABLE `department` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(50) DEFAULT NULL,
  `SORTNUM` varchar(50) DEFAULT NULL,
  `LEVEL` int(11) DEFAULT NULL,
  `NUMBER` varchar(50) DEFAULT NULL,
  `STATE` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='部门表';

员工表：

CREATE TABLE `employee` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(50) DEFAULT NULL,
  `EMPNO` varchar(50) DEFAULT NULL,
  `JOBSTATE` int(11) DEFAULT NULL,
  `STATE` int(11) DEFAULT NULL,
  `SEX` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工表';

部门关系表：

CREATE TABLE `department_relations` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `PID` int(11) NOT NULL,
  `CID` int(11) NOT NULL,
  `STATE` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='部门关系表';

部门-员工关系表

CREATE TABLE `employee_department` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `EID` int(11) NOT NULL,
  `DID` int(11) NOT NULL,
  `STATE` int(11) DEFAULT NULL,
  `RID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工部门关联表';

创建函数getChildLst：用于查出部门下的所有子部门

CREATE DEFINER=`root`@`localhost` FUNCTION `getChildLst`(rootId INT) RETURNS varchar(1000) CHARSET utf8
BEGIN 
  DECLARE sTemp VARCHAR(1000); 
  DECLARE sTempChd VARCHAR(1000); 
  SET sTemp = '$'; 
  SET sTempChd =cast(rootId as CHAR);  
  WHILE sTempChd is not null DO     
  SET sTemp = concat(sTemp,',',sTempChd); 
       SELECT group_concat(cid) INTO sTempChd FROM department_relations where FIND_IN_SET(pid,sTempChd)>0; 
       END WHILE; 
       RETURN sTemp; 
  END
;;
DELIMITER ;

输入语句：

select getChildLst(1);

可以查出该部门下的所有子部门（包括该部门）

 

查出该部门下所有员工（包括子部门）

SELECT e.NAME AS ename ,d.ID AS did ,d.NAME AS dname FROM employee e
LEFT JOIN employee_department ed ON ed.STATE=0
LEFT JOIN department d ON d.STATE=0
WHERE e.STATE=0 AND FIND_IN_SET(ed.DID,getChildLst(3))
AND ed.EID=e.ID AND d.ID=ed.DID