SQL Server Find nth parent that matches some condition

kmb7vmvb 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(76)

I have a table that has a structure similar to the following:
| Id | Parent Id | Condition |
| ------------ | ------------ | ------------ |
| 1 | 0 | 0 |
| 2 | 1 | 0 |
| 3 | 0 | 0 |
| 4 | 3 | 0 |
| 5 | 3 | 0 |
| 6 | 3 | 1 |
| 7 | 6 | 0 |
| 8 | 6 | 1 |
| 9 | 8 | 0 |
| 10 | 9 | 0 |

where Condition is a bit that indicates whether the row meets a specific condition. in most cases the condition is 0, but sometimes it is 1.

I want to return the Id and the parent where condition is 0, which can be 1 or more levels above the immediate parent. if I just wanted immediate parent, it is already inside the table, but in the example above I would like to return a parent Id of 3 for Id 7, even though 7's immediate parent Id is 6, but 6 matches some condition so it's next parent would be 3. here is what I would like the results to look like:
| Id | NonConditionParent |
| ------------ | ------------ |
| 1 | 0 |
| 2 | 1 |
| 3 | 0 |
| 4 | 3 |
| 5 | 3 |
| 6 | 3 |
| 7 | 3 |
| 8 | 3 |
| 9 | 3 |
| 10 | 9 |

I created the following sql with a recursive CTE, but it just returns the immediate parent of the id, which the table already contains:

WITH NON_CONDITION_PARENT(Id,ParentId) AS
(
    SELECT M.Id, M.ParentId
    FROM [Mytable] M 

    UNION ALL
    
    SELECT M2.Id, M2.ParentId
    FROM [Mytable] as M2 
    inner join NON_CONDITION_PARENT on M2.ParentId=NON_CONDITION_PARENT.Id
    WHERE M2.Condition = 0
)
SELECT * FROM NON_CONDITION_PARENT

I'm not entirely sure a recursive CTE is the right thing for this, but I know recursion plays a part - can someone point me in the right direction? or help correct my CTE?

I thought about getting all the parents that match a condition then getting the min parent id for a group, but I still don't know how to get all parents for a single id and return it along with the fileid

sql-server

来源：https://stackoverflow.com/questions/77348520/find-nth-parent-that-matches-some-condition

1条答案

按热度按时间

eoxn13cs1#

You can try to create udf to get the NonConditionParent . Here is the sample code

CREATE FUNCTION GetNonConditionParent(@Id INT)
RETURNS INT
AS
BEGIN
    DECLARE @Parent INT;
    SELECT @Parent = ParentId
FROM MyTable
WHERE Id = @Id;

WHILE EXISTS (SELECT 1 FROM MyTable WHERE Id = @Parent AND Condition = 1)
BEGIN
    SELECT @Parent = ParentId
    FROM MyTable
    WHERE Id = @Parent;
END

RETURN @Parent;
END;

And then you can select with CTE like you have

WITH CTE AS (
    SELECT Id, ParentId, Condition
    FROM MyTable
    WHERE Condition = 0
    UNION ALL
    SELECT M.Id, M.ParentId, M.Condition
    FROM MyTable M
    INNER JOIN CTE ON M.Id = CTE.ParentId
)
SELECT DISTINCT Id, dbo.GetNonConditionParent(Id) AS NonConditionParent
FROM CTE
ORDER BY Id;

Or, you can simply select without CTE like this

SELECT DISTINCT Id, dbo.GetNonConditionParent(Id) AS NonConditionParent
FROM MyTable
ORDER BY Id;

Without distinct works too

SELECT  Id, dbo.GetNonConditionParent(Id) AS NonConditionParent
FROM MyTable
ORDER BY Id;

Here is the output (same output for all codes above):

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Find nth parent that matches some condition

1条答案

相关问题

热门标签

最新问答