寻找良久,终于实现,记个笔记:
–SqlServer 2005 CTE
with cte (deptid,dptname,parentid) as (
–起始条件
select a.deptid,a.dptname,a.parentid from base_dept a where a.deptid = 2
union all
–CTE每次递归条件
select a.deptid,a.dptname,a.parentid from base_dept a, cte where a.parentid = cte.deptid
)
–DTE必须紧跟着SQL语句使用。
select deptid,dptname,parentid from cte order by dptname;