sqlserver 递归查询

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

IF OBJECT_ID('tempdb.dbo.#Company') IS NOT NULL DROP TABLE #Company;
CREATE TABLE #Company
(
    CompanyID INT NOT NULL
    PRIMARY KEY,
    ParentCompanyID INT NULL,
    CompanyName VARCHAR(25) NOT NULL
);
INSERT #Company
(CompanyID, ParentCompanyID, CompanyName)
    VALUES (1, NULL, 'Mega-Corp'),
    (2, 1, 'Mediamus-Corp'),
    (3, 1, 'KindaBigus-Corp'),
    (4, 3, 'GettinSmaller-Corp'),
    (5, 4, 'Smallest-Corp'),
    (6, 5, 'Puny-Corp'),
    (7, 5, 'Small2-Corp');

select * from #Company

递归查询语句

WITH CompanyTree(ParentCompanyID, CompanyID, CompanyName, CompanyLevel) AS
(
-- Anchor Member
SELECT ParentCompanyID,
        CompanyID,
        CompanyName,
        0 AS CompanyLevel
FROM #Company
WHERE ParentCompanyID IS NULL
UNION ALL
-- Recursive Member
SELECT c.ParentCompanyID,
        c.CompanyID,
        c.CompanyName,
        p.CompanyLevel + 1
FROM #Company c
    INNER JOIN CompanyTree p
    ON c.ParentCompanyID = p.CompanyID
)
SELECT ParentCompanyID,
    CompanyID,
    CompanyName,
    CompanyLevel
    FROM CompanyTree;

结果展示

查询过程分析