sql函数： 多级树状目录-根据父ID查询出所有的子ID

一. 简介

           比如说部门，有上级部门，是多级的，需要根据部门ID查询出所有该部门的子部门。

           如果是2级，可以通过左连接联查同一张表即可。

           但层级数不确定的情况下，就得用sql函数或者存储过程来实现了。

           当然，也可以使用find_in_set函数来实现。

二. 函数方式

2.1 sql数据脚本：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for apartment
-- ----------------------------
DROP TABLE IF EXISTS `apartment`;
CREATE TABLE `apartment`  (
  `aid` bigint(20) NOT NULL AUTO_INCREMENT,
  `apartment_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称',
  `description` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门描述',
  `parent_aid` bigint(20) NULL DEFAULT NULL COMMENT '父部门ID',
  `state` int(11) NULL DEFAULT NULL COMMENT '状态 0 删除 1 正常',
  `create_uid` bigint(20) NULL DEFAULT NULL COMMENT '创建人ID',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_uid` bigint(20) NULL DEFAULT NULL COMMENT '修改人ID',
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`aid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '部门表' ROW_FORMAT = Compact;

-- ----------------------------
-- Records of apartment
-- ----------------------------
INSERT INTO `apartment` VALUES (0, '总部门', '总部门', NULL, 1, 0, '2018-08-22 20:32:45', NULL, NULL);
INSERT INTO `apartment` VALUES (5, '语音业务部', '管语音的部门', 0, 1, 0, '2018-08-23 15:40:10', NULL, NULL);
INSERT INTO `apartment` VALUES (6, '短信业务部', '管语音的部门', 0, 1, 0, '2018-08-23 15:41:14', 0, '2018-08-24 10:27:24');
INSERT INTO `apartment` VALUES (13, 'CAAS项目组', NULL, 5, 1, 0, '2018-08-24 10:29:59', NULL, NULL);
INSERT INTO `apartment` VALUES (14, '软件项目组', NULL, 5, 1, 0, '2018-08-24 10:30:51', NULL, NULL);

SET FOREIGN_KEY_CHECKS = 1;

2.2 函数定义

CREATE DEFINER=`root`@`%` FUNCTION `selectApartmentChildIdList`(apartmentId INT) RETURNS varchar(4000) CHARSET utf8
BEGIN

	DECLARE aidListStr VARCHAR(4000);
	DECLARE tempAid VARCHAR(4000);

	SET aidListStr = '';
	SET tempAid = CAST(apartmentId AS CHAR);

	WHILE tempAid IS NOT NULL
	DO
		SET aidListStr = CONCAT( aidListStr, ',', tempAid );
		SELECT GROUP_CONCAT(aid) INTO tempAid FROM apartment WHERE state > 0 AND FIND_IN_SET(parent_aid,tempAid) > 0;
	END WHILE;
	
	RETURN SUBSTRING( aidListStr, 2 );
END

2.3 测试

三. 存储过程方式TODO

 

四. FIND_IN_SET方式

4.1 sql脚本

CREATE TABLE `sys_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(128) NOT NULL COMMENT '角色名称',
  `level` tinyint(4) NOT NULL COMMENT '层级：从1开始',
  `parent_id` bigint(20) NOT NULL COMMENT '父角色ID，顶级为0',
  `state` tinyint(4) NOT NULL COMMENT '状态：0-删除，1-启用，2-禁用',
  `remark` varchar(256) DEFAULT NULL COMMENT '备注',
  `create_info` varchar(256) DEFAULT NULL COMMENT '创建信息',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_info` varchar(256) DEFAULT NULL COMMENT '修改信息',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8



insert into `sys_role` (`id`, `role_name`, `level`, `parent_id`, `state`, `remark`, `create_info`, `create_time`, `update_info`, `update_time`) values('1','超级管理员','1','0','1','超级管理员最高级','system','2020-02-20 16:48:08','system','2020-02-20 16:48:13');
insert into `sys_role` (`id`, `role_name`, `level`, `parent_id`, `state`, `remark`, `create_info`, `create_time`, `update_info`, `update_time`) values('2','研发部','2','1','2','超级管理员-研发部','system','2020-02-20 16:51:14','system','2020-02-22 08:49:53');
insert into `sys_role` (`id`, `role_name`, `level`, `parent_id`, `state`, `remark`, `create_info`, `create_time`, `update_info`, `update_time`) values('3','产品部','2','1','1','超级管理员-产品部','system','2020-02-22 07:45:54',NULL,NULL);
insert into `sys_role` (`id`, `role_name`, `level`, `parent_id`, `state`, `remark`, `create_info`, `create_time`, `update_info`, `update_time`) values('4','销售部','2','1','1','超级管理员-销售部','system','2020-02-22 07:55:17',NULL,NULL);
insert into `sys_role` (`id`, `role_name`, `level`, `parent_id`, `state`, `remark`, `create_info`, `create_time`, `update_info`, `update_time`) values('5','产品总监','3','3','1','超级管理员-产品部-产品总监','system','2020-02-22 07:46:12',NULL,NULL);
insert into `sys_role` (`id`, `role_name`, `level`, `parent_id`, `state`, `remark`, `create_info`, `create_time`, `update_info`, `update_time`) values('6','销售总监','3','4','1','超级管理员-销售部-销售总监','system','2020-02-22 07:55:31',NULL,NULL);

 

 

4.2 写法

SELECT 
	t3.id
FROM (
	SELECT 
		t1.id, t1.parent_id, 
		t2.*,
		IF(FIND_IN_SET(t1.parent_id, @pids) > 0, @pids := CONCAT(@pids, ',', t1.id), 0) AS isChild
	FROM (
		SELECT id, parent_id FROM sys_role WHERE state > 0 ORDER BY parent_id, id
	) AS t1,
	( SELECT @pids := 此处修改为要查询的ID ) AS t2
	
) t3	
WHERE t3.isChild != 0

 

4.3 测试

• 将 此处修改为要查询的ID  修改为 1，  则会将ID为1的所有的子ID查询出来：
  • 
• 将 此处修改为要查询的ID  修改为3， 则会将ID为3的所有的子ID查询出来：
  • 

4.4 步骤分解

• 
•  
• 
• 
• 

 

4.5 拓展

• 查询指定节点的所有父节点ID

• SELECT 
  	t3.id
  FROM (
  
  	SELECT 
  		t1.id, t1.parent_id, 
  		t2.*,
  		IF(FIND_IN_SET(t1.id, @pids) > 0, @pids := CONCAT(@pids, ',', t1.parent_id), 0) AS isParent
  	FROM (
  		SELECT id, parent_id FROM sys_role WHERE state > 0 ORDER BY parent_id DESC, id DESC
  	) t1,
  	( SELECT @pids := 指定节点的ID ) t2 
  
  ) t3
  
  WHERE t3.isParent != 0 AND t3.id != 指定节点的ID

•  测试如下图