1.由根目录查所有子目录

WITH locs(MENU_ID,MENU_NAME_CH,PARENT_MENU_ID,loclevel)
AS
(
    SELECT A.MENU_ID,A.MENU_NAME_CH,A.PARENT_MENU_ID,0 AS loclevel FROM SYS_MENU_INFO A 
    where a.menu_id=75
UNION ALL
        SELECT A.MENU_ID,A.MENU_NAME_CH,A.PARENT_MENU_ID,loclevel+1 FROM SYS_MENU_INFO A  
            INNER JOIN locs p ON a.PARENT_MENU_ID=p.MENU_ID
 
)
SELECT * FROM locs

2.由子目录开始查询

WITH locs(MENU_ID,MENU_NAME_CH,PARENT_MENU_ID,loclevel)
AS
(
    SELECT A.MENU_ID,A.MENU_NAME_CH,A.PARENT_MENU_ID,0 AS loclevel FROM SYS_MENU_INFO A 
    where a.menu_id=78
UNION ALL
        SELECT A.MENU_ID,A.MENU_NAME_CH,A.PARENT_MENU_ID,loclevel+1 FROM SYS_MENU_INFO A  
            INNER JOIN locs p ON p.PARENT_MENU_ID=a.MENU_ID
 
)
SELECT * FROM locs

sql实现子查询