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