(转)有关树形结构的查询--Sql

树形结构是一类重要的非线性结构，在关系型数据库中如何对具有树形结构的表进行查询，从而得到所需的数据是一个常见的问题。
    关系型数据库将数据按表结构形式进行组织。它对表格的处理方便灵活，且易学易用，因而得到广泛的应用。关系型数据库所处理的表格是线性结构的，表的每一行对应着一个数据元素，称做一条记录。记录与记录之间呈线性排列，彼此间没有联系。
   然而，在解决实际问题时，常常会遇到非线性结构的数据。如下表所示，每一条纪录中的上级代码，就和其他纪录有着联系，这样就形成了一棵具有层次结构的树，它可以用下面的图来形象地表示： 
图1
  树形结构是一种结点之间有分支，并具有层次关系的结构，它非常类似于自然界中的树。 树结构在客观世界中大量存在，例如家谱、行政组织机构都可用树形象地表示。树在计算机领域中也有着广泛的应用，例如在编译程序中，用树来表示源程序的语法结构；在数据库系统中，用树来组织信息；在分析算法的行为时，用树来描述其执行过程。
    在关系数据库中对具有树型结构的表常用的3种查询：
1. 节点A的位于第n层的父亲节点的信息。如：员工黄菁菁的两级上司的信息。 
  --创建函数GetManager  

CREATE FUNCTION dbo.GetManager ( @employee_id AS char(5), @level AS int = 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 ) 

2．某棵子树的统计信息，如：员工余顺景及其所有下属员工的工资总额。 
    同样使用递归的算法：
    如果没有下属，则返回当前的工资额; 如果有下属，则返回所有下属的工资总额。  

    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 

--员工逍遥及其下属的工资总额
SELECT dbo.GetTotalSalary('E9906') AS 工资总额

3. 某棵子树的结点信息，如：员工郑可可及其所有下属员工的信息  
   a.求树的深度：
CREATE FUNCTION dbo.GetUnderlyingLevel ( @manager_id AS char(5) ) 
RETURNS int AS 
BEGIN 
   RETURN 
  CASE 
  WHEN EXISTS(SELECT * FROM [员工信息] WHERE [上级号码] = @manager_id) 
  THEN 1 + (SELECT MAX(dbo.GetUnderlyingLevel([员工号码])) FROM [员工信息] WHERE [上级号码] = @manager_id) 
  ELSE 1 
  END 
END 

   如：SELECT dbo.GetUnderlyingLevel('E9901') AS '下属级别'
  ---返回：4

  b 求某一个节点所有的子节点的信息。
    由于返回的是一个结果集，所以需要用table数据类型来存储
CREATE FUNCTION dbo.GetSubtreeInfo ( @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 
) 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')  
--结果：
  
  c .对b进行改进，将该树型结构以图形化的方式打印出来。 
     由于打印要进行排序，所以加了一个标记字段。
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 组织结构 FROM dbo.GetSubtreeInfo2('E9901') order by [标记]
--结果： 

图2