将递归CTE与另一个查询组合

Question

我有一个位置表，每个位置都可以有一个父位置

LocationId | ParentLocationId
-----------------------------
     1              null
     2                1
     3                2
     4                2

我设法创建了一个递归CTE，它为任何给定的位置id提供父位置id (加上原始位置id

WITH       GetLocationParents AS
  (
    select    [LocationId],    [ParentLocationId]   from    Locations
    where     LocationId = 3
    UNION ALL 
    select    i.[LocationId],    i.[ParentLocationId]
    from        Locations i 
    join  GetLocationParents cte on cte.ParentLocationId = i.LocationId
  )
SELECT  [ParentLocationId] FROM GetLocationParents
WHERE   [ParentLocationId] is not NULL;

例如，where LocationId = 3将返回：

ParentLocationId
----------------
       3
       2
       1

在另一个表中，我有一个查询，它将返回LocationId作为其中一个字段：

select exi.PersonId, exi.LocationId from Persons e
left join PersonHasLocations exi on e.PersonId = exi.PersonId
left join Locations i on exi.LocationId = i.LocationId

如果使用where子句，它将返回如下内容：

PersonId | LocationId
---------------------
    100          3

我尝试将这些查询组合在一起以获得结果：

PersonId | LocationId
---------------------
   100        3
   100        2
   100        1

我尝试执行以下命令，但仍然只返回第一行：

WITH
    GetLocationParents AS
        (select    [LocationId],    [ParentLocationId]   from    Locations
        --where LocationId = 3
        UNION ALL 
        select    i.[LocationId],    i.[ParentLocationId]
        from    Locations i    inner join GetLocationParents cte 
        on cte.ParentLocationId = i.LocationId),
    GetPersons AS
        (select exi.PersonId, exi.LocationID from Persons e
        left join PersonHasLocations exi on e.PersonID = exi.PersonId
        left join Locations i on exi.LocationId = i.LocationID)
SELECT * FROM GetLocationParents gip
INNER JOIN GetPersons ge on ge.LocationId = gip.LocationID
WHERE ge.PersonId = 100

像这样将递归查询与普通查询合并是可能的吗？

Answer 1

我猜你的cte里有个小bug。我建议将查询更改如下：

DECLARE @t TABLE (
  LocationId int,
  ParentLocationId int
)

INSERT INTO @t VALUES
    (1, NULL)
   ,(2, 1)
   ,(3, 2)
   ,(4, 2)

;WITH       GetLocationParents AS
  (
    select    [LocationId] AS k, [LocationId],    [ParentLocationId]   from    @t
    UNION ALL 
    select    k, i.[LocationId],    i.[ParentLocationId]
    from        GetLocationParents cte
    join @t i   on cte.ParentLocationId = i.LocationId
  )
SELECT  *
 FROM GetLocationParents
 WHERE k = 3

这样，您就会收到一个列表，在第一列中包含您筛选的值，在第二列中包含该值以上的所有依赖“级别”。然后可以使用它来连接到您的第二个表。

请记住，根据您的级别数量，您必须注意MAX RECUSRSION。