SQL Server中的树结构数据查询 [英] Tree structure data query in SQL Server

问题描述

我有一个表 Person ，有三列： Id，Name，ParentId 其中 ParentId 是父行的 Id 。

目前，整个树，它必须循​​环遍历所有的子元素，直到没有更多的子元素。似乎效率不高。

有更好更有效的查询这些数据的方法吗？

另外，有没有更好的方式来表示这个树状结构在SQL Server数据库？我的表/数据库的替代设计？

解决方案

我不认为设计有什么问题，父母子女关系水平有限。下面是使用递归CTE检索关系的一个例子：

  USE tempdb; 
 GO 
 
 CREATE TABLE dbo.tree 
（
 ID INT PRIMARY KEY，
 name VARCHAR（32），
 ParentID INT FOREIGN KEY参考dbo.tree（ID）
）; 
 
 INSERT dbo.tree SELECT 1，'grandpa'，NULL 
 UNION ALL SELECT 2，'dad'，1 
 UNION ALL SELECT 3，'me'，2 
 UNION ALL SELECT 4，'mom'，1 
 UNION ALL SELECT 5，'grandma'，NULL; 
 
; WITH x AS 
（
  -  anchor：
 SELECT ID，name，ParentID，[level] = 0 
 FROM dbo.tree WHERE ParentID IS NULL 
 UNION ALL 
  - 递归：
 SELECT t.ID，t.name，t.ParentID，[level] = x。[level] + 1 
 FROM x INNER JOIN dbo.tree AS t 
 ON t.ParentID = x.ID 
）
 SELECT ID，name，ParentID，[level] FROM x 
 ORDER BY [level] 
 OPTION（MAXRECURSION 32）; 
 GO 
  

不要忘记清理：

  DROP TABLE dbo.tree; 
  

这可能是一篇有用的文章。另一种方法是 hierarchyid ，但我发现对于大多数情况来说它过于复杂。 / p>

I have a table Person that has 3 columns: Id, Name, ParentId where ParentId is the Id of the parent row.

Currently, to display the entire tree, it would have to loop through all child elements until there's no more child elements. It doesn't seem too efficient.

Is there a better and more efficient way to query this data?

Also, is there a better way to represent this tree like structure in a SQL Server database? An alternative design for my table/database?

解决方案

I don't think there's anything wrong with the design, assuming you have a limited level of parent-child relationships. Here is a quick example of retrieving the relationship using a recursive CTE:

USE tempdb;
GO

CREATE TABLE dbo.tree
(
    ID INT PRIMARY KEY,
    name VARCHAR(32),
    ParentID INT FOREIGN KEY REFERENCES dbo.tree(ID)
);

INSERT dbo.tree SELECT 1, 'grandpa', NULL
UNION ALL SELECT 2, 'dad', 1
UNION ALL SELECT 3, 'me', 2
UNION ALL SELECT 4, 'mom', 1
UNION ALL SELECT 5, 'grandma', NULL;

;WITH x AS
(
    -- anchor:
    SELECT ID, name, ParentID, [level] = 0
    FROM dbo.tree WHERE ParentID IS NULL
    UNION ALL
    -- recursive:
    SELECT t.ID, t.name, t.ParentID, [level] = x.[level] + 1
    FROM x INNER JOIN dbo.tree AS t
    ON t.ParentID = x.ID
)
SELECT ID, name, ParentID, [level] FROM x
ORDER BY [level]
OPTION (MAXRECURSION 32);
GO

Don't forget to clean up:

DROP TABLE dbo.tree;

This might be a useful article. An alternative is hierarchyid but I find it overly complex for most scenarios.

这篇关于SQL Server中的树结构数据查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！