sqlserver 递归查询

1.首先创建临时表模拟数据

 1 IF OBJECT_ID('tempdb.dbo.#Company') IS NOT NULL DROP TABLE #Company;
 2 CREATE TABLE #Company
 3 (
 4     CompanyID INT NOT NULL
 5     PRIMARY KEY,
 6     ParentCompanyID INT NULL,
 7     CompanyName VARCHAR(25) NOT NULL
 8 );
 9 INSERT #Company
10 (CompanyID, ParentCompanyID, CompanyName)
11     VALUES (1, NULL, 'Mega-Corp'),
12     (2, 1, 'Mediamus-Corp'),
13     (3, 1, 'KindaBigus-Corp'),
14     (4, 3, 'GettinSmaller-Corp'),
15     (5, 4, 'Smallest-Corp'),
16     (6, 5, 'Puny-Corp'),
17     (7, 5, 'Small2-Corp');
18 
19 select * from #Company

递归查询语句

 1 WITH CompanyTree(ParentCompanyID, CompanyID, CompanyName, CompanyLevel) AS
 2 (
 3 -- Anchor Member
 4 SELECT ParentCompanyID,
 5         CompanyID,
 6         CompanyName,
 7         0 AS CompanyLevel
 8 FROM #Company
 9 WHERE ParentCompanyID IS NULL
10 UNION ALL
11 -- Recursive Member
12 SELECT c.ParentCompanyID,
13         c.CompanyID,
14         c.CompanyName,
15         p.CompanyLevel + 1
16 FROM #Company c
17     INNER JOIN CompanyTree p
18     ON c.ParentCompanyID = p.CompanyID
19 )
20 SELECT ParentCompanyID,
21     CompanyID,
22     CompanyName,
23     CompanyLevel
24     FROM CompanyTree;

结果展示

查询过程分析