萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

UDF 處理層次資料 (四):查詢整棵子樹與管理鏈

四、查詢管理鏈

前面範例皆為標量 UDF,現在介紹返回表值的 UDF。處理層次資料時,常見需求是返回指定管理員為根的整棵子樹。Listing 6 顯示建立 ufn_GetSubtree 的腳本。

Listing 6:獲得整棵子樹

CREATE FUNCTION ufn_GetSubtree
(
    @mgrid AS int
)
RETURNS @tree table
(
    empid int NOT NULL,
    mgrid int NULL,
    empname varchar(25) NOT NULL,
    salary money NOT NULL,
    lvl int NOT NULL,
    path varchar(900) NOT NULL
)
AS
BEGIN
    DECLARE @lvl AS int, @path AS varchar(900)
    SELECT @lvl = 0, @path = '.'

    INSERT INTO @tree
    SELECT empid, mgrid, empname, salary,
        @lvl, '.' + CAST(empid AS varchar(10)) + '.'
    FROM Employees
    WHERE empid = @mgrid

    WHILE @@ROWCOUNT > 0
    BEGIN
        SET @lvl = @lvl + 1
        INSERT INTO @tree
        SELECT E.empid, E.mgrid, E.empname, E.salary,
            @lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
        FROM Employees AS E JOIN @tree AS T
        ON E.mgrid = T.empid AND T.lvl = @lvl - 1
    END
    RETURN
END

返回表結構同 Employees,額外增加 lvl (層級,從 0 起算) 和 path (格式 .id0.id1...idn.,包含從根到當前節點的所有員工 ID,前後以點號包圍)。path 可讓子樹行正確排序:下屬的 path 以管理員的 path 為前綴,排序時自然出現在管理員之後。

測試範例

取得 Andrew (empid=2) 及其所有下屬:

SELECT * FROM ufn_GetSubtree(2) ORDER BY path

取得全員層次圖:

SELECT REPLICATE(' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1) ORDER BY path

圖一顯示查詢結果的層次結構視覺化。

copyright © 萬盛學電腦網 all rights reserved