树节点查询

-------------------建表--------------------------------------------------

IF OBJECT_ID('tb') IS NOT NULL
    DROP TABLE tb
GO
CREATE TABLE tb
    (
      id CHAR(3) ,
      pid CHAR(3) ,
      NAME NVARCHAR(10)
    )
GO
INSERT  tb
        SELECT  '001' ,
                NULL ,
                '中国'
        UNION ALL
        SELECT  '002' ,
                '001' ,
                '北京'
        UNION ALL
        SELECT  '003' ,
                '001' ,
                '湖南'
        UNION ALL
        SELECT  '004' ,
                '002' ,
                '海淀区'
        UNION ALL
        SELECT  '005' ,
                '002' ,
                '朝阳区'
        UNION ALL
        SELECT  '006' ,
                '002' ,
                '西城区'
        UNION ALL
        SELECT  '007' ,
                '003' ,
                '长沙'
        UNION ALL
        SELECT  '008' ,
                '003' ,
                '株洲'
        UNION ALL
        SELECT  '009' ,
                '003' ,
                '湘潭'
GO

-------------创建函数--------------------------------------
IF OBJECT_ID(N'f_Cid', N'TF') IS NOT NULL
    DROP FUNCTION f_Cid
GO
CREATE FUNCTION f_Cid ( @Id CHAR(3) )
RETURNS @t_Child TABLE ( Id CHAR(3), cLevel INT )
AS
    BEGIN
        DECLARE @level INT
        SET @level = 1
        INSERT  @t_Child
                SELECT  @id ,
                        @level
        WHILE @@ROWCOUNT > 0
            BEGIN
                SET @level = @level + 1
                INSERT  @t_Child
                        SELECT  a.id ,
                                @level
                        FROM    tb a ,
                                @t_Child b
                        WHERE   a.pid = b.Id
                                AND b.cLevel = @level - 1
            END
        RETURN
    END
GO

---------------sql2000下查询第二层某个节点的下面的所有子节点-------------------------------
SELECT  *
FROM    TB a ,
        f_Cid('002') b
WHERE   a.id = b.id
go
-----------------sql2005CTE方式查询中国下面所有子节点（不包含自身）-------------------------
WITH    t AS ( SELECT   id ,
                        pid ,
                        name
               FROM     TB
               WHERE    id = '001'
               UNION ALL
               SELECT   b.id ,
                        b.pid ,
                        b.name
               FROM     TB b
                        INNER JOIN t p ON b.pid = p.id
             )
    SELECT  *
    FROM    t
    WHERE   id <> '001'