[SQL] CTE + 遞迴

http://pastebin.com/1ZSKASZa#

CREATE TABLE dbo.company
(companyid INT NOT NULL PRIMARY KEY,
parentcompanyid INT NULL,
companyname VARCHAR(25) NOT NULL)
INSERT company (companyid, parentcompanyid, companyname)
VALUES (1, NULL, 'AAA-Corp')
INSERT company (companyid, parentcompanyid, companyname)
VALUES (2, 1, 'BBB-Corp')
INSERT company (companyid, parentcompanyid, companyname)
VALUES (3, 1, 'CCC-Corp')
INSERT company (companyid, parentcompanyid, companyname)
VALUES (4, 3, 'DDD-Corp')
INSERT company (companyid, parentcompanyid, companyname)
VALUES (5, 4, 'EEE-Corp')
INSERT company (companyid, parentcompanyid, companyname)
VALUES (6, 5, 'FFF-Corp')
INSERT company (companyid, parentcompanyid, companyname)
VALUES (7, 5, 'GGG-Corp')
SELECT * FROM company
WITH companyStructure(Parent, CompanyID, CompanyName, CompanyLevel)
AS(
        SELECT parentcompanyid
                ,companyid
                ,companyname
                ,0 AS CompanyLevel
        FROM company
        WHERE parentcompanyid IS NULL
        UNION ALL
        SELECT c.parentcompanyid
                ,c.companyid
                ,c.companyname
                ,s.CompanyLevel + 1
        FROM company c
        JOIN companyStructure s ON c.parentcompanyid = s.companyid
        )
SELECT Parent, CompanyID, CompanyName, CompanyLevel
FROM companyStructure
--防止無線迴圈
OPTION (MAXRECURSION 10)