oracle 查询（4，5，6....）级部门 对应的 3级部门 sql

Question

oraclesql

Answer 1

根节点还好弄点，要第三级的，呵呵
还好你的字段中存储了部门的层级可以简化一点
select connect_by_root(departId),connect_by_root(departName),departId
from departInfo
where connect_by_isleaf=1
start with departLvl>3
connect by prior  parentDepartId=departId

Answer 2

SELECT DISTINCT departid, parentdepartid, departlvl, departname
  FROM departinfo di
 WHERE di.departlvl = 3
 START WITH di.departlvl > 3
CONNECT BY PRIOR parentdepartid = departid

Answer 3

发现少写了个条件，呵呵
select connect_by_root(departId),connect_by_root(departName),departId
from departInfo
where connect_by_isleaf=1
start with departLvl>3
connect by prior  parentDepartId=departId and prior departlvl>3