递归找父级或子级

sql向上递归 (查找包含自己的父级id等信息)

SELECT T2.id,T2.dep_name,T2.dep_level,T2.dep_type

FROM (

SELECT

@r AS _id,

(

SELECT @r := parent_id

FROM dep_info

WHERE id = _id) AS parent_id,

@l := @l + 1 AS lvl

FROM (

SELECT @r := #{id,jdbcType = INTEGER}, @l := 0) vars,

dep_info h

WHERE @r != 0 AND parent_id > 0

) T1

JOIN dep_info T2 ON T1._id = T2.id

ORDER BY T1.lvl DESC

 

sql向下递归  （查找包含自己的子级id）

SELECT id

FROM (

SELECT t1.id, IF(FIND_IN_SET(parent_id, @pids) > 0, @pids := CONCAT(@pids, ',', id), 0) AS ischild

FROM (

SELECT id,parent_id

FROM dep_info

WHERE 1=1 AND state = 'NORMAL'

ORDER BY parent_id, id

) t1,

(

SELECT @pids := #{deptId,jdbcType=INTEGER}) t2

) t3

WHERE ischild != '0' order by id

 

 

java递归找父级

DepInfoBean depInfoBean = depInfoBeanDao.selectByPrimaryKey(depId);

while (depInfoBean.getDepLevel() > 3) {
    DepInfoBean parentDepInfoBean = depInfoBeanDao.selectByPrimaryKey(depInfoBean.getParentId());
    if (parentDepInfoBean.getDepType().equals(DepTypeEnum.PFC.getCode()) && parentDepInfoBean.getDepLevel().equals(3)) {
        resultDto.setZoneName(parentDepInfoBean.getDepName());
        resultDto.setZoneCode(parentDepInfoBean.getDepCode());

        //根据部门名 模糊搜索省
        String depProvince = this.basicAreaDAO.executeSelectOneMethod(depInfoBean.getDepName(), "selectProvinceNameByCon", String.class);
        resultDto.setProvinceName(depProvince);
        return resultDto;
    } else {
        depInfoBean = parentDepInfoBean;
    }
}