如何在 SQL Server 2005 中实现高性能的树形视图

2022-01-05 00:00:00 sql database tree sql-server

构建代表树的表格的最佳方法是什么?我想实现一个可以很好地处理大数据的选择、插入、更新和删除.例如，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 是分支的根.

相关文章