SQL2005 节点查询问题

[ybice]

如何用一条SQL语名查询某一结点下所有子结点
例如表 t 字段
id parentid
1 0
2 1
3 1
4 2
5 0
6 5
7 4
我想得到某一结点如 1 下所有子结点 1,2,3,4,7
用一条sql语句能不能实现 不用函数

[--小F--]

;with f as
(
select * from tb where id=1
union all
select a.* from tb a, f b where a.parentid=b.id
)
select * from f

[htl258_Tony]

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-06-04 14:35:26
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog   : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [int],[parentid] [int])
INSERT INTO [tb]
SELECT '1','0' UNION ALL
SELECT '2','1' UNION ALL
SELECT '3','1' UNION ALL
SELECT '4','2' UNION ALL
SELECT '5','0' UNION ALL
SELECT '6','5' UNION ALL
SELECT '7','4'

--SELECT * FROM [tb]

-->SQL查询如下:
;WITH t AS
(
    SELECT * 
    FROM tb
    WHERE id = 1
    UNION ALL
    SELECT a.* 
    FROM tb a, t b
    WHERE a.parentid = b.id
)
SELECT ID 
FROM t
/*
ID
-----------
1
2
3
4
7

(5 行受影响)
*/

[ybice]

速度真快