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

Question

我有这些记录的表：

+----+-------------+---------+ 
| 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 | 
+----+-------------+---------+ 

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

感谢您阅读

Answer 1

我觉得父节点必须在它的子节点之前。 这里是我的查询

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执行功能

SELECT * FROM dbo.DisplayTree(-1) 

Answer 2

您需要拆分到n的列的列数，然后需要使用ORDER BY那些列。

模式：

CREATE TABLE #TAB(ID INT, Name VARCHAR(20),ParentID INT) 

INSERT INTO #TAB 
SELECT 1, 'Item 1', -1 
UNION ALL 
SELECT 2, 'Item 2' , -1 
UNION ALL 
SELECT 3, 'Item 1.1' , 1 
UNION ALL 
SELECT 4, 'Item 1.2' , 1 
UNION ALL 
SELECT 5, 'Item 2.1' , 2 
UNION ALL 
SELECT 6, 'Item 1.1.1', 4 
UNION ALL 
SELECT 7, 'Item 1.2.1', 6 
UNION ALL 
SELECT 8, 'Item 2.2', 2 

我转换Name列XML，然后我使用XML方法value

;WITH CTE AS 
( 
    SELECT 
     ID, 
     Name, 
     ParentID, 
     CAST('<M>'+REPLACE (REPLACE(Name,' ','.'),'.','</M><M>')+'</M>' AS XML) 
     AS XML_SPLT 
    FROM #TAB 
) 
SELECT 
    ID, 
    Name, 
    ParentID, 
    XML_SPLT.value('/M[1]', 'varchar(50)') As P0, 
    XML_SPLT.value('/M[2]', 'int') As P1, 
    XML_SPLT.value('/M[3]', 'int') As P2, 
    XML_SPLT.value('/M[4]', 'int') As P3 

FROM CTE 
ORDER BY P0,P1,P2,P3 
GO 

这会给你的命令，你期待做列的标签。

+----+------------+----------+------+----+------+------+ 
| ID | Name | ParentID | P0 | P1 | P2 | P3 | 
+----+------------+----------+------+----+------+------+ 
| 1 | Item 1  |  -1 | Item | 1 | NULL | NULL | 
| 3 | Item 1.1 |  1 | Item | 1 | 1 | NULL | 
| 6 | Item 1.1.1 |  4 | Item | 1 | 1 | 1 | 
| 4 | Item 1.2 |  1 | Item | 1 | 2 | NULL | 
| 7 | Item 1.2.1 |  6 | Item | 1 | 2 | 1 | 
| 2 | Item 2  |  -1 | Item | 2 | NULL | NULL | 
| 5 | Item 2.1 |  2 | Item | 2 | 1 | NULL | 
| 8 | Item 2.2 |  2 | Item | 2 | 2 | NULL | 
+----+------------+----------+------+----+------+------+