使用 CTE 的。

给定树状表结构：

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

$ b b

，此查询将返回 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 = @parent 替换 parent = 0

如果在 table（parent）上有一个索引，因为它将递归使用 INDEX LOOKUP 来为每个父项查找所有chilrden。

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.

更新某个分支，发出：

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 是分支的根。