用SQL实现树的查询(二)

　　由于该查询返回的是一个结果集，因此已经不能使用递归的方法来实现，我们使用循环的方法来实现，循环的过程为：将参数 @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) 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’) 

　　最后我们来看一个有趣的例子。将上面的函数稍做修改后，可以将该树型结构以图形化的方式打印出来。

　　完整的函数如下所示：

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 [标记]