如何在 SQL Server 2005 中实现高性能的树形视图
构建代表树的表格的最佳方法是什么?我想实现一个可以很好地处理大数据的选择、插入、更新和删除.例如,select 必须支持Expand ALL" - 获取给定节点的所有子节点(以及那里的子节点).
What is the best way to build the table that will represent the tree? I want to implement a select ,insert ,update and delete that will work well with big data. The select for example will have to support "Expand ALL" - getting all the children (and there children) for a given node.
推荐答案
使用 CTE
's.
给定树状表结构:
id parent name
1 0 Electronics
2 1 TV
3 1 Hi-Fi
4 2 LCD
5 2 Plasma
6 3 Amplifiers
7 3 Speakers
,这个查询将返回id
、parent
和深度级别,以树的形式排序:
, this query will return id
, parent
and depth level, ordered as a tree:
WITH v (id, parent, level) AS
(
SELECT id, parent, 1
FROM table
WHERE parent = 0
UNION ALL
SELECT id, parent, v.level + 1
FROM v
JOIN table t
ON t.parent = v.id
)
SELECT *
FROM v
id parent name
1 0 Electronics
2 1 TV
4 2 LCD
5 2 Plasma
3 1 Hi-Fi
6 3 Amplifiers
7 3 Speakers
将 parent = 0
替换为 parent = @parent
以仅获取树的一个分支.
Replace parent = 0
with parent = @parent
to get only a branch of a tree.
如果在 table (parent)
上有一个索引,这个查询将有效地处理一个非常大的表,因为它将递归地使用 INDEX LOOKUP
来查找所有子项每个家长.
Provided there's an index on table (parent)
, this query will efficiently work on a very large table, since it will recursively use INDEX LOOKUP
to find all chilrden for each parent.
要更新某个分支,请发出:
To update a certain branch, issue:
WITH v (id, parent, level) AS
(
SELECT id, parent, 1
FROM table
WHERE parent = 0
UNION ALL
SELECT id, parent, v.level + 1
FROM v
JOIN table t
ON t.parent = v.id
)
UPDATE table t
SET column = newvalue
WHERE t.id IN
(
SELECT id
FROM v
)
其中 @parent
是分支的根.
相关文章