时间戳

首页 > 解决方案 > 将递归 CTE 与另一个查询相结合

sql - 将递归 CTE 与另一个查询相结合

问题描述

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

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

是否可以将递归查询与这样的普通查询合并？

标签： sqlsql-server

解决方案

我猜你的 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。

推荐阅读