使用SQL Server表，我需要创建一个递归的新表？

I have a simple table of related items, like so (SQL Server db)

我有一个简单的相关项目表,如此(SQL Server数据库)

id   Item   Parent
1     2      5
2     4      5
3     5      12
4     6      2
5     10     6

I'd like to output a table that shows, for each Item a full path of all inter-related items (up to 4 "levels"), like so

我想输出一个表格,为每个项目显示所有相互关联项目的完整路径(最多4个“级别”),如此

id  Item  ParentL1  ParentL2 ParentL3 ParentL4
1    2      5         12
2    4      5         12
3    5      12
4    6      2         5         12
5    10     6         2          5     12

Thanks!

3 个解决方案

#1

This is the simple approach.

这是一种简单的方法。

SELECT id, t1.Item as Item, 
   t1.Parent as ParentL1,  
   t2.Parent as ParentL2, 
   t3.Parent as ParentL3, 
   t4.Parent as ParentL4
FROM Items t1
LEFT JOIN Items t2 ON t1.Parent = t2.Id
LEFT JOIN Items t3 ON t2.Parent = t3.Id
LEFT JOIN Items t4 ON t3.Parent = t4.Id

#2

The follwoing query should do the trick

以下查询应该可以解决问题

SELECT t1.id, t1.Item, t1.Parent [ParentL1], t2.Parent [ParentL2], t3.Parent [ParentL3], t4.Parent [ParentL4]
FROM MyTable t1
LEFT JOIN MyTable t2
    ON t1.Parent = t2.Item
    LEFT JOIN MyTable t3
        ON t2.Parent = t3.Item
        LEFT JOIN MyTable t4
            ON t3.Parent = t4.Item

Used the following to create the test table, MyTable to confirm the resultset

使用以下命令创建测试表MyTable以确认结果集

CREATE TABLE MyTable
(
id      Int IDENTITY,
Item    Int,
Parent  Int
)

INSERT MyTable
VALUES (2, 5),
        (4, 5),
        (5, 12),
        (6, 2),
        (10, 6)

#3

Ok, even though the LEFT JOINs are the simplest way in this case (when only 4 levels of recursion are needed), this is another option using recursive CTEs (SQL Server 2005+):

好吧,即使LEFT JOIN是这种情况下最简单的方法(当只需要4级递归时),这是使用递归CTE的另一种选择(SQL Server 2005+):

;WITH CTE AS 
(
    SELECT *, 1 RecursionLevel
    FROM YourTable
    UNION ALL
    SELECT B.id, A.Item, B.Parent, RecursionLevel + 1
    FROM CTE A
    INNER JOIN YourTable B
        ON A.Parent = B.Item
)
SELECT  Item,
        MIN(CASE WHEN RecursionLevel = 1 THEN Parent END) ParentL1,
        MIN(CASE WHEN RecursionLevel = 2 THEN Parent END) ParentL2,
        MIN(CASE WHEN RecursionLevel = 3 THEN Parent END) ParentL3,
        MIN(CASE WHEN RecursionLevel = 4 THEN Parent END) ParentL4
FROM CTE
WHERE RecursionLevel <= 4
GROUP BY Item

This is the result:

这是结果:

╔══════╦══════════╦══════════╦══════════╦══════════╗
║ Item ║ ParentL1 ║ ParentL2 ║ ParentL3 ║ ParentL4 ║
╠══════╬══════════╬══════════╬══════════╬══════════╣
║    2 ║        5 ║ 12       ║ NULL     ║ NULL     ║
║    4 ║        5 ║ 12       ║ NULL     ║ NULL     ║
║    5 ║       12 ║ NULL     ║ NULL     ║ NULL     ║
║    6 ║        2 ║ 5        ║ 12       ║ NULL     ║
║   10 ║        6 ║ 2        ║ 5        ║ 12       ║
╚══════╩══════════╩══════════╩══════════╩══════════╝

And here is a sqlfiddle with a demo of this.

这是一个带有演示的sqlfiddle。

#1