SQL查询递归

WITH DEPARTMENT AS

(

--定位点成员定义

SELECT *,0 AS LEVEL

FROM PE_FC_FileCategory WHERE CategoryID=3 --【当前部门ID】

UNION ALL

--递归成员定义

SELECT E.*,Level+1

FROM PE_FC_FileCategory AS E

INNER JOIN DEPARTMENT AS D ON E.ParentID=D.CategoryID

)

SELECT * from DEPARTMENT OPTION(maxrecursion 100)