有关树形结构的查询--Sql

原文地址:http://springjava.iteye.com/blog/592579

 树形结构是一类重要的非线性结构,在关系型数据库中如何对具有树形结构的表进行查询,从而得到所需的数据是一个常见的问题。
    关系型数据库 将数据按表结构形式进行组织。它对表格的处理方便灵活,且易学易用,因而得到广泛的应用。关系型数据库所处理的表格是线性结构的,表的每一行对应着一个数据元素,称做一条记录。记录与记录之间呈线性排列,彼此间没有联系。
   然而,在解决实际问题时,常常会遇到非线性结构的数据。如下表所示,每一条纪录中的上级代码,就和其他纪录有着联系,这样就形成了一棵具有层次结构的树,它可以用下面的图来形象地表示:

  树形结构 是一种结点之间有分支,并具有层次关系的结构,它非常类似于自然界中的树。树结构在客观世界中大量存在,例如家谱、行政组织机构都可用树形象地表示。树在计算机领域中也有着广泛的应用,例如在编译程序中,用树来表示源程序的语法结构;在数据库系统中,用树来组织信息;在分析算法的行为时,用树来描述其执行过程。
    在关系数据库中对具有树型结构的表常用的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 [标记]
--结果: 

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭