sql实现子查询
2014-09-19 15:10 转身寻找静谧 阅读(289) 评论(0) 编辑 收藏 举报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
