sqlserver实现oracle递归树函数样例(start with.....connect by prior)

 

所要用到的两张表：

 

sql语句：

sqlserver中的：

with stepCTE AS ( SELECT G.treecode,G.PARENTID, G.GROUPID,1  AS Lev FROM MIS_GROUPINFO G WHERE G.GROUPID IN (SELECT GROUPID FROM MIS_GROUPINFOADMIN WHERE USERID = '00000001') UNION ALL SELECT a.treecode,a.PARENTID,a.GROUPID ,CTE.Lev + 1 FROM MIS_GROUPINFO a inner join StepCTE CTE ON CTE.GROUPID = a.PARENTID ) --此后跟的插入或查询语句 select * from stepCTE order by treecode 

oracle中的： 

 

SELECT GROUPID, treecode  FROM MIS_GROUPINFO START WITH GROUPID IN (SELECT GROUPID FROM MIS_GROUPINFOADMIN                         WHERE USERID = '00000002')CONNECT BY PRIOR GROUPID = PARENTID order by treecode

效果图：