1.sqlserver:获取本级及下级部门
WITH cte as(
select deptId,deptName,superdeptid from ts_cttsp_dept where deptid=(select groups from ts_cttsp_staffInfo where staffNum=#{assignee,jdbcType=VARCHAR})
union all
select s.deptId,s.deptName,s.superdeptid from ts_cttsp_dept s
inner join cte t on s.superdeptid=t.deptId
)
select deptid from cte
2.oracle 递归
现在要递归查询冶炼(id=2)的所有父节点和子节点:
- SELECT * FROM tree START WITH id = 2 CONNECT BY PRIOR pid = id -- 递归查询父节点
- union
- SELECT * FROM tree START WITH id = 2 CONNECT BY pid = PRIOR id; -- 递归查询子节点
当然,也可以使用in关键字递归查询多个节点的父节点和子节点:
- SELECT * FROM tree START WITH id in (1, 10) CONNECT BY PRIOR pid = id -- 递归查询父节点
- union
- SELECT * FROM tree START WITH id in (1, 10) CONNECT BY pid = PRIOR id; -- 递归查询子节点
3.mysql查找所有的父节点
SELECT T2.`USER_ID`,T2.`USERNAME`
FROM (
SELECT
@r AS _id,
(SELECT @r := IMMEDIATE_SUPERIOR FROM USERS WHERE USER_ID = _id) AS PARENT_ID,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 469912346530152448, @l := "0") vars,
USERS h
WHERE @r <> 0) T1
LEFT JOIN USERS T2
ON T1._id = T2.USER_ID
WHERE T2.USER_ID != 469912346530152448