SQL 如何在 MSSQL 2005 中创建递归查询？

How do I create a recursive query in MSSQL 2005?

提问by Pablo Retyk

Let's say I have the following table:

假设我有下表：

CustomerID ParentID Name
========== ======== ====
1          null     John
2          1        James
3          2        Jenna
4          3        Jennifer
5          3        Peter
6          5        Alice
7          5        Steve
8          1        Larry 

I want to retrieve in one query all the descendants of James (Jenna,Jennifer,Peter, Alice, Steve). Thanks, Pablo.

我想在一个查询中检索 James 的所有后代（Jenna、Jennifer、Peter、Alice、Steve）。谢谢，巴勃罗。

回答by mathieu

On SQL Server 2005 you can use CTEs (Common Table Expressions):

在 SQL Server 2005 上，您可以使用CTE（公用表表达式）：

with Hierachy(CustomerID, ParentID, Name, Level)
as
(
select CustomerID, ParentID, Name, 0 as Level
    from Customers c
    where c.CustomerID = 2 -- insert parameter here
    union all
    select c.CustomerID, c.ParentID, c.Name, ch.Level + 1
    from Customers c
    inner join Hierachy ch
    on c.ParentId = ch.CustomerID
)
select CustomerID, ParentID, Name
from Hierachy
where Level > 0

回答by zozzancs

For bottom up use mathieu's answer with a little modification:

对于自下而上，使用 mathieu 的答案稍加修改：

with Hierachy(CustomerID, ParentID, Name, Level)
as
(
select CustomerID, ParentID, Name, 0 as Level
    from Customers c
    where c.CustomerID = 2 -- insert parameter here
    union all
    select c.CustomerID, c.ParentID, c.Name, ch.Level + 1
    from Customers c
    inner join Hierachy ch

    -- EDITED HERE --
    on ch.ParentId = c.CustomerID
    ----------------- 

)
select CustomerID, ParentID, Name
from Hierachy
where Level > 0

回答by Jasper Bekkers

You can't do recursion in SQL without stored procedures. The way to solve this is using Nested Sets, they basically model a tree in SQL as a set.

如果没有存储过程，就不能在 SQL 中进行递归。解决这个问题的方法是使用嵌套集，它们基本上将 SQL 中的树建模为一个集合。

Notice that this will require a change to the current data model or possibly figuring out how to create a view on the original model.

请注意，这将需要对当前数据模型进行更改，或者可能需要弄清楚如何在原始模型上创建视图。

Postgresql example (using very few postgresql extensions, just SERIAL and ON COMMIT DROP, most RDBMSes will have similar functionality):

Postgresql 示例（使用很少的 postgresql 扩展，只有 SERIAL 和 ON COMMIT DROP，大多数 RDBMS 将具有类似的功能）：

Setup:

设置：

CREATE TABLE objects(
    id SERIAL PRIMARY KEY,
    name TEXT,
    lft INT,
    rgt INT
);

INSERT INTO objects(name, lft, rgt) VALUES('The root of the tree', 1, 2);

Adding a child:

添加一个孩子：

START TRANSACTION;

-- postgresql doesn't support variables so we create a temporary table that 
-- gets deleted after the transaction has finished.

CREATE TEMP TABLE left_tmp(
    lft INT
) ON COMMIT DROP; -- not standard sql

-- store the left of the parent for later use
INSERT INTO left_tmp (lft) VALUES((SELECT lft FROM objects WHERE name = 'The parent of the newly inserted node'));

-- move all the children already in the set to the right
-- to make room for the new child
UPDATE objects SET rgt = rgt + 2 WHERE rgt > (SELECT lft FROM left_tmp LIMIT 1);
UPDATE objects SET lft = lft + 2 WHERE lft > (SELECT lft FROM left_tmp LIMIT 1);

-- insert the new child
INSERT INTO objects(name, lft, rgt) VALUES(
    'The name of the newly inserted node', 
    (SELECT lft + 1 FROM left_tmp LIMIT 1), 
    (SELECT lft + 2 FROM left_tmp LIMIT 1)
);

COMMIT;

Display a trail from bottom to top:

从下到上显示轨迹：

SELECT
    parent.id, parent.lft
FROM
    objects AS current_node
INNER JOIN
    objects AS parent
ON
    current_node.lft BETWEEN parent.lft AND parent.rgt
WHERE
    current_node.name = 'The name of the deepest child'
ORDER BY
    parent.lft;

Display the entire tree:

显示整个树：

SELECT
    REPEAT('   ', CAST((COUNT(parent.id) - 1) AS INT)) || '- ' || current_node.name AS indented_name
FROM
    objects current_node
INNER JOIN
    objects parent
ON
    current_node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY
    current_node.name,
    current_node.lft
ORDER BY
    current_node.lft;

Select everything down from a certain element of the tree:

从树的某个元素中选择所有内容：

SELECT
    current_node.name AS node_name
FROM
    objects current_node
INNER JOIN
    objects parent
ON
    current_node.lft BETWEEN parent.lft AND parent.rgt
AND
    parent.name = 'child'
GROUP BY
    current_node.name,
    current_node.lft
ORDER BY
    current_node.lft;

回答by Kaniu

Unless I'm missing something, recursion isn't necessary...

除非我遗漏了什么，否则不需要递归......

SELECT d.NAME FROM Customers As d
INNER JOIN Customers As p ON p.CustomerID = d.ParentID
WHERE p.Name = 'James'