如何在Sql Server 2000中用一条语句实现树的查询

以公司的网站栏目表为例，表结构（表名：Subject）如下：

字段名称 字段类型 字段长度
id int 4
NAME varchar 200
PARENTID int 4
CODE varchar 100

在数据库中创建一个自定义函数：

CREATE FUNCTION dbo.GetSubtreeInfo
( @manager_id AS char(5)
)
RETURNS @treeinfo table
( [id] [int] NOT NULL ,
[name] [varchar] (100) NOT NULL,
[parentid] [int] NOT NULL,
[varchar] (100) NULL,
[levels] [int] NOT NULL
) AS
BEGIN
DECLARE @level AS int
SELECT @level = 0
INSERT INTO @treeinfo
SELECT [id], [name], [parentid], , @level
FROM [subject]
WHERE [id] = @manager_id
WHILE @@ROWCOUNT > 0
BEGIN
SET @level = @level + 1
INSERT INTO @treeinfo
SELECT E.[id], E.[name], E.[parentid], E., @level
FROM [subject] AS E JOIN @treeinfo AS T
ON E.[parentid] = T.[id] AND T.[levels] = @level - 1
END
RETURN
END

说明：其中的levels是树状结构的级数
使用方法：select SPACE(levels*3)+name ,* from GetSubtreeInfo(973) 其中973可以修改为你要开始查询的起始节点
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/david286/archive/2007/04/27/1587411.aspx






--不包含根节点

declare @Id int
set @Id = 68; ---再次修改父节点
With RootNodeCTE(Id,fatherid,cname)
As (Select categoryid,fatherid,name From u1club_gdm_category Where fatherid In (@Id)
Union All
Select u1club_gdm_category.categoryid,u1club_gdm_category.fatherid,u1club_gdm_category.name From RootNodeCTE
Inner Join u1club_gdm_category
On RootNodeCTE.Id = u1club_gdm_category.fatherid )
Select * From RootNodeCTE