SQL Server 存储过程 - 如何以树格式对记录进行排序？

debugcn 发表于 Dev

博格巴

我有一个包含这些记录的表：

+----+-------------+---------+
| ID | Name        | ParentID|
+----+-------------+---------+
|  1 | Item 1      |   -1    |
|  2 | Item 2      |   -1    |
|  3 | Item 1.1    |    1    |
|  4 | Item 1.2    |    1    |
|  5 | Item 2.1    |    2    |
|  6 | Item 1.1.1  |    3    |
|  7 | Item 1.2.1  |    4    |
|  8 | Item 2.2    |    2    |
|  9 | Item 1.1.1.1|    6    |
+----+-------------+---------+

我想选择树格式的记录。

如何使用存储过程获得如下表所示的结果？Name列的值是临时的，可以是任何字。

+----+-------------+---------+
| ID | Name        | ParentID|
+----+-------------+---------+
|  1 | Item 1      |   -1    |
|  3 | Item 1.1    |    1    |
|  6 | Item 1.1.1  |    3    |
|  9 | Item 1.1.1.1|    6    |
|  4 | Item 1.2    |    1    |
|  7 | Item 1.2.1  |    4    |
|  2 | Item 2      |   -1    |
|  5 | Item 2.1    |    2    |
|  8 | Item 2.2    |    2    |
+----+-------------+---------+

对不起，我是存储过程的初学者。所以我不知道如何得到上表的结果。

感谢您阅读

三伏

我认为父节点必须在其子节点之前。这是我的查询

DECLARE @SampleData AS TABLE( ID int,  Name   varchar(20)  , ParentID int)

INSERT INTO @SampleData VALUES (  1 ,' Item 1',   -1    )
INSERT INTO @SampleData VALUES ( 2 ,' Item 2'      ,   -1    )
INSERT INTO @SampleData VALUES (  3 ,' Item 1.1'    ,    1    )
INSERT INTO @SampleData VALUES (  4 ,' Item 1.2'    ,    1    )
INSERT INTO @SampleData VALUES (  5 ,' Item 2.1'    ,   2    )
INSERT INTO @SampleData VALUES (  6 ,' Item 1.1.1'  ,   4    )
INSERT INTO @SampleData VALUES (  7 ,' Item 1.2.1'  ,    6    )
INSERT INTO @SampleData VALUES (  8 ,' Item 2.2'    ,    2    )

;with cte as (
    select t.Id, t.Name, t.ParentID, 1 as lev, t.Id AS RootId
    from @SampleDAta t
    where t.ParentID = -1
    union all
    select t.ID, t.Name,t.ParentID,cte.lev +1, cte.RootId 
    from cte 
    INNER JOIN @SampleDAta t on t.ParentID = cte.Id
    )
SELECT c.Id, c.Name, c.ParentID FROM cte c
ORDER BY c.RootId, c.lev  
OPTION (MAXRECURSION 0)

结果：

如果你想像深度搜索树一样排序，我可以通过一个函数来完成

CREATE TABLE SampleData ( ID int,  Name   varchar(20)  , ParentID int)

INSERT INTO SampleData VALUES (  1 ,' Item 1',   -1    )
INSERT INTO SampleData VALUES ( 2 ,' Item 2'      ,   -1    )
INSERT INTO SampleData VALUES (  3 ,' Item 1.1'    ,    1    )
INSERT INTO SampleData VALUES (  4 ,' Item 1.2'    ,    1    )
INSERT INTO SampleData VALUES (  5 ,' Item 2.1'    ,   2    )
INSERT INTO SampleData VALUES (  6 ,' Item 1.1.1'  ,   3    )
INSERT INTO SampleData VALUES (  7 ,' Item 1.2.1'  ,    4    )
INSERT INTO SampleData VALUES (  8 ,' Item 2.2'    ,    2    )

现在创建函数

CREATE FUNCTION DisplayTree
(
    @RootId int
)
RETURNS 
@result TABLE (
    ID int,  Name   varchar(20)  , ParentID int
    )
AS
BEGIN
    DECLARE @Temp AS TABLE 
    (
        ID int,  Name   varchar(20)  , ParentID int
    )
    INSERT INTO @Temp SELECT * FROM SampleData WHERE ParentID = @RootId

    WHILE(EXISTS(SELECT 1 FROM @Temp t))
    BEGIN
        DECLARE @CurrentRootId int
        SELECT TOP 1 @CurrentRootId = t.ID FROM @Temp t ORDER BY t.ID ASC
        INSERT INTO @result SELECT * FROM @Temp t WHERE t.ID = @CurrentRootId
        DELETE FROM @Temp WHERE ID = @CurrentRootId

        INSERT INTO @result SELECT * FROM dbo.DisplayTree(@CurrentRootId)
    END

    RETURN ;
END
GO

AND 执行函数