Mysql 递归部门查询员工

2017-07-26 17:29:57

CREATE TABLE `depts` (
`id`  int NOT NULL AUTO_INCREMENT ,
`parent_id`  int NOT NULL DEFAULT 0 ,
`type_id`  int NOT NULL ,
`dept_name`  varchar(20) NOT NULL DEFAULT '' ,
PRIMARY KEY (`id`)
);
CREATE TABLE `users` (
`id`  int NOT NULL AUTO_INCREMENT ,
`dept_id`  int NOT NULL DEFAULT 0 ,
`user_name`  varchar(20) NOT NULL DEFAULT '' ,
PRIMARY KEY (`id`)
);
INSERT INTO `depts` (`parent_id`, `type_id`, `dept_name`) VALUES ('0', '1', 'TG');
INSERT INTO `depts` (`parent_id`, `type_id`, `dept_name`) VALUES ('0', '1', 'SG');
INSERT INTO `depts` (`parent_id`, `type_id`, `dept_name`) VALUES ('1', '2', ' 研发部');
INSERT INTO `depts` (`parent_id`, `type_id`, `dept_name`) VALUES ('2', '1', '产品部');
INSERT INTO `depts` (`parent_id`, `type_id`, `dept_name`) VALUES ('1', '5', '运维安全线');
INSERT INTO `depts` (`parent_id`, `type_id`, `dept_name`) VALUES ('6', '2', '安全');
INSERT INTO `depts` (`parent_id`, `type_id`, `dept_name`) VALUES ('6', '2', '网络');
INSERT INTO `users` (`dept_id`, `user_name`) VALUES ('3', '小明');
INSERT INTO `users` (`dept_id`, `user_name`) VALUES ('3', '小红');
INSERT INTO `users` (`dept_id`, `user_name`) VALUES ('3', '小张');
INSERT INTO `users` (`dept_id`, `user_name`) VALUES ('4', '老张');
INSERT INTO `users` (`dept_id`, `user_name`) VALUES ('4', '老王');
INSERT INTO `users` (`dept_id`, `user_name`) VALUES ('4', '小黑');
INSERT INTO `users` (`dept_id`, `user_name`) VALUES ('5', '小黄');
INSERT INTO `users` (`dept_id`, `user_name`) VALUES ('7', '大虾');
INSERT INTO `users` (`dept_id`, `user_name`) VALUES ('8', '天空');
INSERT INTO `users` (`dept_id`, `user_name`) VALUES ('8', '玛丽');
#诚迈(腾讯外包)题目:编写SQL 查找出以下表中 TG的所有员工。
#实现
delimiter // 
CREATE FUNCTION `getChildFormDeptNameList`(rootId INT)
RETURNS varchar(1000) 
BEGIN
    DECLARE sRet VARCHAR(1000);
    DECLARE sStr VARCHAR(1000); 

    SET sRet = '$';
    SET sStr =cast(rootId as CHAR);

    WHILE sStr is not null DO
        SET sRet = concat(sRet,',',sStr);
        SELECT group_concat(id) INTO sStr FROM depts where FIND_IN_SET(parent_id,sStr)>0;
    END WHILE;
RETURN sRet; 
END
//
select u.*,d.dept_name from users as u left join depts as d on u.dept_id = d.id where FIND_IN_SET(d.id,getChildFormDeptNameList(1));