sql server 2000 實現樹形結構表格式查詢

樹形結構是一類重要的非線性結構，在關係型資料庫中如何對具有樹形結構的表進行查詢，從而得到所需的資料是一個常見的問題。
本文筆者以 SQL Server 2000為例，就一些常用的查詢給出了相應的演算法與代碼，頗值得讀者借鑒。

關係型資料庫將資料按表結構形式進行組織。它對錶格的處理方便靈活，且易學易用，因而得到廣泛的應用。關係型資料庫所處理的表格是線

性結構的，表的每一行對應著一個資料元素，稱做一條記錄。記錄與記錄之間呈線性排列，彼此間沒有聯絡，然而，在解決實際問題時，
常常會遇到非線性結構的資料。如下表所示，每一條紀錄中的上級代碼，就和其他紀錄有著聯絡，這樣就形成了一棵具有階層的樹，
它可以用下面的圖來形象地表示： 

樹形結構是一種結點之間有分支，並具有層次關係的結構，它非常類似於自然界中的樹。樹結構在客觀世界中大量存在，例如家譜、
行政組織機構都可用樹形象地表示。樹在電腦領域中也有著廣泛的應用，例如在編譯器中，用樹來表示來源程式的文法結構；
在資料庫系統中，用樹來組織資訊；在分析演算法的行為時，用樹來描述其執行過程。
在關係型資料庫中如何對具有樹形結構的表進行查詢，從而得到所需的資料是一種常見的需求。下面以SQLServer 2000 為例，
就三種常用的查詢給出相應的演算法與代碼：

1．節點 A 的位於第 n層的父結點資訊，如：員工黃菁菁的上兩級上司的名稱。
2．某棵子樹的統計資訊，如：員工餘順景及其所有下屬員工的工資總額。
3．某棵子樹的結點資訊，如：員工鄭可可及其所有下屬員工的名稱。

某節點的父節點資訊 
 要實現這樣的查詢，常使用遞迴的方法。我們可以用SQL Server 2000增加的使用者定義函數 （UDF, User Defined Function）

這個新特性來實現遞迴函式調用。下面是函數的定義：

CREATE FUNCTION dbo.GetManager ( @employee_idAS char(5), @level AS int = 1 -- 預設值為1 )
  RETURNS char(5)

其中，employee_id表示要查詢的員工號碼，level表示高於該員工的層級數，返回的結果是上司的員工號碼。
該函數的遞迴定義為：
  如果 level = 0，則返回當前的員工號碼；
  如果 level > 0，則返回直接上司的 level-1級的上司號碼。
根據這樣的遞迴定義，我們可以寫出完整的遞迴函式：
CREATE FUNCTION dbo.GetManager ( @employee_id AS char(5), @level ASint = 1 )RETURNS char(5)
AS
BEGIN
  IF @level = 0
    RETURN @employee_id
  ——如果 level 為0，表示已經找到其上司號碼
  RETURN dbo.GetManager( (SELECT [上級號碼] FROM[員工資訊] WHERE [員工號碼] = @employee_id),
    @level -1)-- 如果 level 大於 0，則返回直接上司的 level-1 級的上司號碼
END
執行下面的語句可以得到需要的結果：
SELECT * FROM [員工資訊] WHERE [員工號碼]=dbo.GetManager(‘E9907’, 2)

當然，如果要讓該遞迴函式更為健壯，我們還需要在函數中加入容錯檢查，這裡不再贅述。

某棵子樹的統計資訊
 這個查詢同樣使用遞迴的方法來實現。先看一下函數定義：
  CREATE FUNCTION dbo.GetTotalSalary ( @manager_idAS char(5) ) RETURNS int
其中，@manager_id是要統計的某位上司的員工號碼，返回其所有下屬的工資總額。
 該函數的遞迴定義為：如果沒有下屬，則返回當前的工資額;如果有下屬，則返回所有下屬的工資總額。
根據這樣的遞迴定義，我們可以寫出完整的遞迴函式：
CREATE FUNCTION dbo.GetTotalSalary ( @manager_id AS char(5) )RETURNS int AS
BEGIN
RETURN ( SELECT [工資] FROM [員工資訊] WHERE
  [員工號碼] = @manager_id) +
   CASE  WHEN EXISTS(SELECT * FROM [員工資訊] WHERE[上級號碼] = @manager_id)
       THEN ( SELECT SUM(dbo.GetTotalSalary([員工號碼])) FROM [員工資訊]WHERE [上級號碼] = @manager_id)
    ELSE 0
END
END

上面的自訂使用者函數中使用了CASE搜尋函數，它按指定順序為每個 WHEN 子句的 Boolean_expression求值，返回第一個取值為

TRUE 的 Boolean_expression 的result_expression，如果沒有取值為 TRUE 的Boolean_expression，則當有ELSE子句

時SQL Server將返回 else_result_expression;若沒有ELSE子句，則返回 NULL 值。

在自訂使用者函數中，如果員工資訊表中發現該員工有下屬（EXISTS子查詢），則為每個下屬調用GetTotalSalary函數返回下屬的工資總

額，並用SUM函數求和；反之，則直接返回其工資額。

執行下面的語句可以得到所需的結果：
  SELECT dbo.GetTotalSalary(‘E9902’) AS‘工資總額’
實際工作還可能有這樣的查詢要求，即某名員工一共有多少個下屬層級（包括其自身），如張建平一共有四個下屬層級。用樹的術語來描述，

即求出某棵子樹的深度。可以通過這樣的遞迴函式來實現：

CREATE FUNCTION dbo.GetUnderlyingLevel (@manager_id AS char(5) ) RETURNS int AS
BEGIN
  RETURN
    CASE WHENEXISTS(SELECT * FROM [員工資訊] WHERE [上級號碼] =@manager_id)
        THEN 1 + (SELECT MAX(dbo.GetUnderlyingLevel([員工號碼])) FROM[員工資訊] WHERE [上級號碼]=     

        @manager_id)
    ELSE 1
   END

END

執行下面的語句可以得到所需的結果：
SELECT dbo.GetUnderlyingLevel('E9901') AS ‘下屬層級’
某棵子樹所有子節點資訊
前面的兩種查詢返回的都是標量值，這裡的查詢需返回某棵子樹的所有子節點的資訊，這是一個結果集，需要用table 資料類型來儲存。函

數定義如下：
CREATE FUNCTION dbo.GetSubtreeInfo ( @manager_id AS int )
RETURNS @treeinfo table
( [員工號碼] [char] (5) NOT NULL,
[姓名] [char] (10) NOT NULL,
[年齡] [int] NOT NULL,
[工資] [money] NOT NULL,
[上級號碼] [char] (5) NULL，
[層級] [int] NOT NULL )
其中，@manager_id代表要查詢的上司的員工號碼，返回的是其所有下屬的資訊，這些資訊存放在table 型變數 @treeinfo 中。
由於該查詢返回的是一個結果集，因此已經不能使用遞迴的方法來實現，我們使用迴圈的方法來實現，迴圈的過程為：將參數@manager_id

所代表的上司的資訊插入到表中，賦予層級0；層級增加為1，將所有上級號碼為以上@manager_id 的員工資訊插入到表中；層級增加為2

，將所有上級號碼與第2步插入的記錄中的員工號碼一致的員工資訊插入到表中；依次增加層級，直到找不到上級號碼與前一步插入的紀錄中的

員工號碼一致的員工資訊為止。
為了實現這個迴圈，我們要用系統函數 @@ROWCOUNT來判斷前一步中是否有新的記錄被插入到表中。如果有，則迴圈繼續；如果無，則迴圈

結束。另外，我們在表中增加了一個名為“層級”的欄位，既可以顯示出所在的層級關係，還可以用來代表每一次新插入的記錄，可謂一舉兩得

。完整的函數定義如下：
CREATE FUNCTION dbo.GetSubtreeInfo ( @manager_id AS char(5) )
  RETURNS @treeinfo table
( [員工號碼] [char] (5) NOT NULL,
   [姓名] [char] (10) NOTNULL,
   [年齡] [int] NOT NULL,
   [工資] [money] NOT NULL,
   [上級號碼] [char] (5)NULL,
   [層級] [int] NOT NULL
  ) AS
BEGIN
DECLARE @level AS int
SELECT @level = 0
INSERT INTO @treeinfo
SELECT [員工號碼], [姓名], [年齡], [工資], [上級號碼], @level
FROM [員工資訊]
WHERE [員工號碼] = @manager_id
WHILE @@ROWCOUNT > 0
BEGIN
   SET @level = @level + 1
   INSERT INTO @treeinfo
   SELECT E.[員工號碼], E.[姓名],E.[年齡], E.[工資], E.[上級號碼], @level
    FROM [員工資訊] AS E JOIN @treeinfo AS T
    ON E.[上級號碼] = T.[員工號碼] AND T.[層級] = @level - 1
END
RETURN
END
下面是測試的結果：
SELECT * FROM dbo.GetSubtreeInfo(‘E9903’)
員工號碼 姓名 年齡 工資 上級號碼 層級
-------- --------- ------- --
E9903 鄭可可 38 5000.0000 E9901 0
E9906 肖遙 26 3350.0000 E9903 1
E9907 黃菁菁 22 2800.0000 E9906 2

最後我們來看一個有趣的例子。將上面的函數稍做修改後，可以將該樹型結構以圖形化的方式列印出來，結果如下所示：

完整的函數如下所示：
CREATE FUNCTION dbo.GetSubtreeInfo2 ( @manager_id AS char(5) )RETURNS @treeinfo table
  ( [員工號碼] [char] (5) NOT NULL,
    [姓名][char] (10) NOT NULL,
    [年齡] [int]NOT NULL,
    [工資][money] NOT NULL,
    [上級號碼][char] (5) NULL,
    [層級] [int]NOT NULL,
    [標記][varchar] (200) NOT NULL
  ) AS
BEGIN
DECLARE @level AS int, @path AS varchar(200)
SELECT @level = 0, @path = 'NULL'
INSERT INTO @treeinfo
SELECT [員工號碼], [姓名], [年齡], [工資], [上級號碼], @level,‘NULL->’+ [員工號碼]
FROM [員工資訊]
WHERE [員工號碼] = @manager_id
WHILE @@ROWCOUNT > 0
BEGIN
SET @level = @level + 1
INSERT INTO @treeinfo
SELECT E.[員工號碼], E.[姓名], E.[年齡], E.[工資], E.[上級號碼],@level, T.[標記] + ‘->’+ E.[員工號碼]
FROM [員工資訊] AS E JOIN @treeinfo AS T
｀ ON E.[上級號碼] = T.[員工號碼] AND T.[層級] = @level - 1
END
RETURN
END
使用以下語句，即可返回如上所示的樹型結構：
SELECT REPLICATE (‘ | ’, [層級]) + [姓名] AS 組織圖 FROMdbo.GetSubtreeInfo2(‘E9901’) order by [標記]

 

呵呵 這個表自己去建哦 然後try it!!

引用：

http://blog.sina.com.cn/s/blog_4af2201a01008d6p.html