请教SQL SERVER中的树查询

Arraysherwinzhu

我在做关于角色继承方面的程序。其中由于部分需要查找一个角色的所有子角色。也就是树查询的问题。
我以前用Oracle，其中有树查询，可以很方便的解决这个问题。
但现在数据库是SQL SERVER 。
请问在SQL SERVER 中用怎样的方法可以实现类似的功能。
最好效率可以高一点。

多谢

stormproof

定义一张这样的表有“角色”和“上级角色”两个字段 ，这样就可以进行树查询了。

zjcdillion

select son_no from list where father_no in (select son_no from list where father_no=\'A\')

kitesky

写个递归SP。sql server 提倡使用SP解决问题。

lava_li

我用SQL Server2000，没发现有像Oracle中的树状结构查询，自己写

tclywork

我也想要关于树结构的递归的存储过程.请问哪位愿意提供一个样例过来.

sherwinzhu

中序遍历
比按层次遍历复杂一点。
CREATE FUNCTION dbo.Ufn_GetSubjectAllTreeByPreOrder
-- 用按 中序遍历 的方法，获取整个树
()
RETURNS  @tree_TABLE TABLE (    SubjectId INT,                  -- 定义返回值，表变量。注意，定义了遍历结果的顺序TreeOrder
                                SubjectName NVARCHAR(100),
                                SubjectType INT,
                                PSubjectId INT,
                                SubjectOrder INT,
                                TreeOrder INT IDENTITY)
AS  
    BEGIN
        DECLARE @currNode INT                                   -- 当前树节点的ID
        DECLARE @stack TABLE (  StackIndex INT IDENTITY,        -- 定义表变量，模拟堆栈
                                SubjectId INT)
        DECLARE @stackTop INT                                   -- 定义堆栈指针（使用@stack 表中 StackIndex 字段的最大值模拟）。
                                                                -- 关键是出栈元素的处理，使用直接从@stack中删除的方法。
                                                                -- 这样StackIndex 字段最大值的那行的即表示栈顶元素。其StackIndex字段的值自然就是 堆栈指针
        DECLARE @subjectCursor CURSOR                           -- 定义游标
        DECLARE @nodeId INT                                     -- 节点的ID
        DECLARE @rootCount INT                                  -- 根节点的数目


        -- 初始化栈顶指针
        SET @stackTop = 0


        -- 判断是否存在根节点
        SELECT @rootCount = COUNT (*)
            FROM   Subject
            WHERE  PSubjectId  IS NULL
            

        -- 如果存在根节点，处理根节点。根节点入栈。
        IF (@rootCount > 0)
        BEGIN
            -- 将所有 根节点 压入栈中

            SET @subjectCursor = CURSOR FOR                     -- 定义 根节点 的游标
                SELECT SubjectId
                    FROM Subject
                    WHERE PSubjectId IS NULL
                    ORDER BY SubjectOrder DESC                  -- 注意，SubjectOrder降序入栈，这样可以升序出栈

            -- 打开游标
            OPEN @subjectCursor

            -- 游标前进
            FETCH NEXT FROM @subjectCursor INTO @nodeId

            -- 循环
            WHILE (@@FETCH_STATUS = 0)
            BEGIN
                INSERT INTO @stack (SubjectId)
                        VALUES (@nodeId)

                FETCH NEXT FROM @subjectCursor INTO @nodeId
            END

            -- 关闭游标
            CLOSE @subjectCursor


            -- 堆栈指针赋值
            SELECT @stackTop = Max(StackIndex)
                FROM @stack



            -- 开始前序遍历
            -- 当栈中还有元素
            WHILE (@stackTop > 0)
            BEGIN
                -- （1）处理 栈顶指针 所代表的节点
                -- 获得栈顶指针代表的节点
                SELECT @currNode = SubjectId
                    From @stack
                    Where stackIndex = @stackTop

                -- 处理此节点
                INSERT INTO @tree_TABLE
                    ( SubjectId, SubjectName, SubjectType, PSubjectId, SubjectOrder)
                    ( SELECT SubjectId, SubjectName, SubjectType, PSubjectId, SubjectOrder
                        FROM   Subject
                        WHERE  SubjectId = @currNode)


                -- （2）栈顶元素出栈
                -- 注意处理方法，删除模拟堆栈 的 这个表变量中的这个元素
                DELETE FROM @stack
                    WHERE StackIndex = @stackTop


                -- （3）将 栈顶指针所代表的节点 的 子节点压入栈中。注意入栈顺序。
                SET @subjectCursor = CURSOR FOR
                    SELECT SubjectId
                        FROM Subject
                        WHERE PSubjectId = @currNode
                        ORDER BY SubjectOrder DESC

                -- 打开游标
                OPEN @subjectCursor

                -- 游标前进
                FETCH NEXT FROM @subjectCursor INTO @nodeId

                WHILE (@@FETCH_STATUS = 0)
                BEGIN
                    INSERT INTO @stack (SubjectId)
                            VALUES (@nodeId)
                    FETCH NEXT FROM @subjectCursor INTO @nodeId
                END

                -- 关闭游标
                CLOSE @subjectCursor


                -- （4）重新赋值堆栈指针（因为使用表变量模拟堆栈，且使用自增字段，所以栈顶指针实际上就是表变量的自增字段的最大值）
                SELECT @stackTop =  Max(StackIndex) FROM @stack
            END
            -- 堆栈处理结束



        --销毁游标
        DEALLOCATE @subjectCursor
        END


--    --销毁游标
--    DEALLOCATE @subjectCursor
    RETURN
    END

chichunhua

不知道我給的列子對不對但是我認為還是可以的大家看看
建立測試環境
create table biao(parent char(50),child char(50))
insert into biao values (\'World \',\'Europe\')
insert into biao values (\'World \',\'North America\')
insert into biao values (\'Europe\',\'France \')
insert into biao values (\'France \',\'Paris\')
insert into biao values (\'North America\',\'United States\')
insert into biao values (\'North America\',\'canada\')
insert into biao values (\'United States\',\'New York\')
insert into biao values (\'United States\',\'Washington\')
insert into biao values (\'New York\',\'New York City\')
insert into biao values (\'Washington\',\'Redmond\')
select * from biao
儲存過成
CREATE PROCEDURE expand1 (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1

WHILE @level > 0
BEGIN
   IF EXISTS (SELECT * FROM #stack WHERE level = @level)
      BEGIN
         SELECT @current = item
         FROM #stack
         WHERE level = @level
         SELECT @line = space(@level - 1) + @current
         PRINT @line
         DELETE FROM #stack
         WHERE level = @level
            AND item = @current
         INSERT #stack
            SELECT child, @level + 1
            FROM biao
            WHERE parent = @current
         IF @@ROWCOUNT > 0
            SELECT @level = @level + 1
      END
   ELSE
      SELECT @level = @level - 1
END -- WHILE

咝衑xec expand1 world
結果
world               
North America      
  canada            
  United States     
   Washington      
    Redmond         
   New York         
    New York City   
Europe            
  France            
   Paris            
刪除環境
drop proc expand1
drop table biao

joyceanne

楼上的算法，运行过了，效果不错。
学习ING