向上、向下递归查询

1、#向下递归查询指定部门及其所有子部门

SELECT u2.dept_id
FROM(
SELECT
@ids AS p_ids,
(SELECT @ids := GROUP_CONCAT(dept_id) FROM sys_dept WHERE FIND_IN_SET(parent_id, @ids)) AS c_ids,
@l := @l+1 AS LEVEL
FROM sys_dept, (SELECT @ids := #{deptId}, @l := 0 ) b
WHERE @ids IS NOT NULL
) u1
JOIN sys_dept u2
ON FIND_IN_SET(u2.dept_id, u1.p_ids) where u2.dept_id != #{deptId}

2、#向下递归查询指定部门的所有子部门（不含自己）

SELECT u2.dept_id
FROM(
SELECT
@ids AS p_ids,
(SELECT @ids := GROUP_CONCAT(dept_id) FROM sys_dept WHERE FIND_IN_SET(parent_id, @ids)) AS c_ids,
@l := @l+1 AS LEVEL
FROM sys_dept, (SELECT @ids := #{deptId}, @l := 0 ) b
WHERE @ids IS NOT NULL
) u1
JOIN sys_dept u2
ON FIND_IN_SET(u2.dept_id, u1.p_ids) where
u2.dept_id != #{deptId}

#u2.dept_id != #{deptId} 表示不包含自己

3、#向上递归查询指定部门及其所有父部门

SELECT u2.dept_id
FROM(
SELECT
@ids AS p_ids,
(SELECT @ids := GROUP_CONCAT(parent_id) FROM sys_dept WHERE FIND_IN_SET(dept_id, @ids)) AS c_ids,
@l := @l+1 AS LEVEL
FROM sys_dept, (SELECT @ids := #{deptId}, @l := 0 ) b
WHERE @ids IS NOT NULL
) u1
JOIN sys_dept u2
ON FIND_IN_SET(u2.dept_id, u1.p_ids) where u2.dept_id != #{deptId}

4、#向上递归查询指定部门的所有父部门（不含自己）

SELECT u2.dept_id
FROM(
SELECT
@ids AS p_ids,
(SELECT @ids := GROUP_CONCAT(parent_id) FROM sys_dept WHERE FIND_IN_SET(dept_id, @ids)) AS c_ids,
@l := @l+1 AS LEVEL
FROM sys_dept, (SELECT @ids := #{deptId}, @l := 0 ) b
WHERE @ids IS NOT NULL
) u1
JOIN sys_dept u2
ON FIND_IN_SET(u2.dept_id, u1.p_ids) where
u2.dept_id != #{deptId}

5、java代码递归向上查询（包含自己）：

/**
* 获取用户所属部门的所有上级部门
* @param depts 所有部门
* @param childrenDeptList 用户所属子部门
* @param parentDeptList 用户所属子部门的上级部门
*/
private List<Dept> getParentDeptList(List<Dept> depts, List<Dept> childrenDeptList, List<Dept> parentDeptList) {
if (CollectionUtils.isEmpty(childrenDeptList)) {
return parentDeptList;
}
parentDeptList.addAll(childrenDeptList);
List<Integer> parentIds = childrenDeptList.stream().map(SysDept :: getParentId).filter(Objects :: nonNull).collect(Collectors.toList());
//查询父级部门
List<Dept> parentDepts = depts.stream().filter(x -> parentIds.contains(x.getDeptId())).collect(Collectors.toList());
return getParentDeptList(depts,parentDepts,parentDeptList);
}

6、java代码递归向下查询（包含自己）：

 if(null != deptId) {
//查询指定部门及其所有子部门
List<Integer> ids = new ArrayList<>();
ids.add(deptId);
deptIds = ids;
List<Dept> departments = new ArrayList<>();
departments = queryAllChildrenDeptId(departments, deptIds, tenantId);
if(CollectionUtil.isNotEmpty(departments)){
List<Integer> collect = departments.stream().map(x -> x.getDeptId()).collect(Collectors.toList());
deptIds.addAll(collect);
}
}
public List<Dept> queryAllChildrenDeptId(List<Dept> departments,List<Integer> deptIds,String tenantId){
//根据部门ID查询下级子部门
List<Dept> departmentList = DeptMapper.selectList(new LambdaQueryWrapper<Dept>()
.eq(Dept::getTenantId, tenantId)
.in(Dept::getParentId, deptIds)
.eq(Dept::getDelFlag, "0"));
if (CollectionUtil.isNotEmpty(departmentList)) {
//拿到当前所有部门ID
List<Integer> parentIds = departmentList.stream().map(item -> item.getDeptId()).collect(Collectors.toList());
//拼接子部门查询结果
departments.addAll(departmentList);
return queryAllChildrenDeptId(departments,parentIds,tenantId);
} else {
//如果没有下级部门,就返回,结束递归。
return departments;
}
}

说明：
建议使用代码递归查询实现，使用函数数耗时较长。

最后

以上就是谨慎西装为你收集整理的向上、向下递归查询的全部内容，希望文章能够帮你解决向上、向下递归查询所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。