我有一个包含这些记录的表:
+----+-------------+---------+
| 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 执行函数
SELECT * FROM dbo.DisplayTree(-1)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句