选择Id列表中的顶级parentId，如果所有parentId顶部相同，则返回行

Question

我要把我想要的剧本组合成一个：

SELECT [CategoryId] FROM [dbo].[Unit] where Id in (716,724) 

和

DECLARE @Id INT = 15

;WITH cteGetRootID
As
(

    SELECT
        Id, [Name], ParentId, 1 AS CodePosition
    FROM
        Category WHERE Id = @Id
    UNION All
    SELECT
        ic.Id, ic.[Name], ic.ParentId, CodePosition + 1
    FROM Category ic
    INNER JOIN cteGetRootID cte ON ic.Id = cte.ParentId
)
SELECT  top 1 Id, [Name]  FROM cteGetRootID
ORDER BY CodePosition desc

我希望对第一部分的所有结果执行低代码，如果所有的根都具有相同的根类型，那么我希望从dbo.Unit (716,724)中选择从其中的Id中选择dbo.Unit。

希望它能使人感觉到

Answer 1

您可以在第一次选择联合时使用join介于dbo.Unit和dbo.Category之间：

;WITH cteGetRootID
As
(
    SELECT
        c.Id, c.[Name], c.ParentId, 1 AS CodePosition
    FROM
        Category c 
            INNER JOIN  [dbo].[Unit] u on u.CategoryID = c.Id
    WHERE u.Id in (716,724) 
    UNION All
    SELECT
        ic.Id, ic.[Name], ic.ParentId, CodePosition + 1
    FROM Category ic 
             INNER JOIN cteGetRootID cte ON ic.Id = cte.ParentId
)
SELECT  top 1 Id, [Name]  FROM cteGetRootID
ORDER BY CodePosition desc