查遍了Google、Baidu,都没有结果,可能是关键词不对。只好发挥自己的想象力,对,想象力,终于实现了。
需求是:根节点有N级子节点,显示从根节点到该子节点的所有父子节点。说白了,pageSize是根据根节点数量确定的,而不是根节点+子节点,传统的ROWNUM形式就可以说拜拜了。以下是我自己的实现,如有更好的方案,请指点,不过,我这里有个明显的缺陷,需要某个字段是递增的。
SELECT * FROM (
SELECT
XXX_LEVEL
,ID,XXX_NAME,
CASE XXX_LEVEL WHEN 1 THEN
(SELECT COUNT(1) FROM XXX_XXX_CAT WHERE XXX_LEVEL = 1 AND ID <= DTC.ID)
ELSE 0
END COUNTS
FROM XXX_XXX_CAT DTC
START WITH ID IN (
SELECT ID FROM XXX_XXX_CAT
WHERE
XXX_NAME LIKE 'A%'
) CONNECT BY PRIOR XXX_CODE = PARENT_XXX_CODE
) WHERE COUNTS < 2 AND COUNTS >= 1 OR COUNTS = 0;