有关树形结构的查询--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 [标记]
--结果：