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)