递归Self JOIN SSMS但定义最低节点

Good day all, The code below defines a recursive self join, easy. However, using this method I can only define the most parentNode. In my case, I want to be able to identify the most child node and be able to see all of his parents.

美好的一天，下面的代码定义了一个递归的自联接，很简单。但是，使用此方法我只能定义最多的parentNode。在我的情况下，我希望能够识别最多的子节点，并能够看到他的所有父母。

WITH q AS 
(
    SELECT * FROM [QA].[Node]
    WHERE ParentNodeID IS NULL -- This is where we define the most parent ID
    UNION ALL
    SELECT  m.* FROM [QA].[Node] m
        INNER JOIN q ON m.ParentNodeID = q.ID
    )
SELECT * FROM q

Could someone help me out :) it would be greatly appreciated. I want the ability to define lets say, give the query an id of 7, and have the query return in order from the most parent node being first and the the node we gave being the last entry.

有人可以帮助我:)非常感谢。我希望能够定义让我们说，给查询一个id为7，并让查询从最先父节点开始按顺序返回，我们给出的节点是最后一个条目。

For example, if the table is ancestroy and we have a column that references its self identifying the person dad.

例如，如果表是ancestroy，并且我们有一个列引用其自我标识人爸爸。

I want the result to come back if i tell the query, 'WHERE ID = 50'

如果我告诉查询'WHERE ID = 50'，我希望结果回来

ID, ParentId, Name
(1, NULL, Grandpa),
(99, 1, Dad),
(50, 99, Me)

2 个解决方案

#1

This is an easy example

这是一个简单的例子

DECLARE @mockup TABLE(ID INT, ParentId INT, Name VARCHAR(100));
INSERT INTO @mockup VALUES
(1, NULL, 'Grandpa'),
(99, 1, 'Dad'),
(50, 99, 'Me');

DECLARE @StartForBottomUp INT=50;

WITH recCTE AS
(
    SELECT * FROM @mockup WHERE ID=@StartForBottomUp
    UNION ALL
    SELECT m.*
    FROM @mockup AS m
    INNER JOIN recCTE AS r ON m.ID=r.ParentId
)
SELECT * FROM recCTE ;

I start the anchor-part at the item with the given ID and move up the chain.

我在具有给定ID的项目处启动锚点部分并向上移动链。

#2

This is the recursive CTE that you should be using:

这是您应该使用的递归CTE：

WITH q AS
(
    SELECT ID OriginalID, ID, ParentId, [Name], 1 [Level]
    FROM dbo.YourTable
    UNION ALL
    SELECT q.OriginalID, t.ID, t.ParentId, t.[Name], [Level] + 1
    FROM q
    INNER JOIN dbo.YourTable t
        ON q.ParentId = t.ID
)
SELECT ID, ParentId, [Name], [Level]
FROM q
WHERE OriginalID = 50
;

Here is a live demo with the code.

这是一个包含代码的现场演示。

And the results are:

结果是：

╔════╦══════════╦═════════╦═══════╗
║ ID ║ ParentId ║  Name   ║ Level ║
╠════╬══════════╬═════════╬═══════╣
║ 50 ║ 99       ║ Me      ║     1 ║
║ 99 ║ 1        ║ Dad     ║     2 ║
║  1 ║ NULL     ║ Grandpa ║     3 ║
╚════╩══════════╩═════════╩═══════╝

#1