请问sqlserver中如何实现oracle中的start with父子级菜单查询

[西秦月影]

我原来oracle中的语句如下，现在需要移植到sqlserver中，比较着急，感谢各位大神指点~

select * from (select m.* from mh_System m join mh_system_role rm on m.id=rm.system_id join mh_user_role ur on ur.role_id=rm.role_id where ur.user_id=:userId)m start with m.parentId=1 connect by prior m.id= m.parentId order siblings by id

[gw6328]

应该是能实现，但是你得先解释一下oracle start with是什么效果

[卖水果的net]

-- 刚才好像有个需求一样的帖子 -- 大概这个样子 with m as ( select x.id , x.pid , x.name from mytable x where x.id = 100 -- 这个自已指定 union all select x.id , x.pid , x.name from mytable x,m where x.pid = m.id ) select * from m

[中国风]

看看结果对不

DECLARE @userId INT--声明参数;
;WITH m2
AS
(SELECT
 m.* from mh_System m join mh_system_role rm on m.id=rm.system_id join mh_user_role ur on ur.role_id=rm.role_id 
WHERE ur.user_id=@userId AND m.parentId=1
UNION ALL
SELECT
 m.* from mh_System m join mh_system_role rm on m.id=rm.system_id join mh_user_role ur on ur.role_id=rm.role_id 
  JOIN m2  ON m2.id=m.parentid
)
SELECT * FROM m2 ORDER BY parentid,id