oracle层级数据库结构通过一级找最末级数据,听起来好像不是很明白,示例如下:
1:如下表结构
其中point_pid 为0是一级数据,那么2级指的是 point_pid 值为一级数据的sch_tutl_catg_id(表主键)的值,依次类推,可能会存在多个层级。
有的时候我们需要直接根据一级的主键id找到他的最末级的数据,如下sql 帮你解决:
SELECT sch_tutl_catg_id, point_pid, name
FROM (SELECT t2.sch_tutl_catg_id,
t2.point_pid,
t2.name,
LEAD(LEVELS) OVER(ORDER BY RN) aa,
CASE
WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN
0
ELSE
1
END LEAF
FROM (SELECT ROWNUM rn,
G.SCH_TUTL_CATG_ID,
G.NAME,
G.POINT_PID,
LEVEL LEVELS
FROM SCH_TUTL_CATG G
WHERE G.STATUS = 'A'
AND G.POINT_PID IS NOT NULL
START WITH point_pid = #{schTutlCatgId}
CONNECT BY PRIOR G.sch_tutl_catg_id = G.point_pid) t2)
WHERE leaf = 1
具体可以套用……