Блог

У меня есть таблица местоположений, каждое из которых может иметь родительское местоположение

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

Мне удалось создать рекурсивный CTE, который дает мне идентификатор родительского местоположения (плюс исходный идентификатор местоположения) для любого заданного идентификатора местоположения

 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
  

Возможно ли объединить рекурсивный запрос с обычным запросом, подобным этому?

Я предполагаю, что у вас есть небольшая ошибка в вашем cte. Я бы предложил изменить запрос следующим образом:

 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 .