sql 递归（查找一个节点下的所有节点）

--这是查找菜单  第一行是一级菜单，下面是二级菜单
with temp
as
(
    select MenuCode,ParentCode,MenuName,URL,1 as curMenuLevel
    from sys_menu
    where  (ParentCode is null or ParentCode = '') and  MenuCode='pms'
    union all
    select a.MenuCode,a.ParentCode,a.MenuName,a.URL, b.curMenuLevel+1 As  curMenuLevel
    from sys_menu a  
    inner join temp b on ( a.ParentCode=b.MenuCode)  
)
select * from temp