从关联实体链接具有通用ID的所有ID

一尘不染

从关联实体链接具有通用ID的所有ID

sql

ERD只是为了轻松实现：

表“ Location”中的位置可以通过关联实体“ Link Location”相互链接

假设有一些当前的链接如下所示：

location_id_1       location_id_2       active
1                   5                   True
5                   3                   True
2                   6                   True
4                   6                   True
6                   7                   True

我正在尝试编写一个查询，该查询将返回一列，其中所有ID可能会相互连接，即使被一个或多个链接删除/隔开。因此，将1链接到5，将3链接到5。由于5的公共ID，一旦删除，则1也链接到3。

因此，在我的查询中，如果您愿意，我希望能够确定一个“主要位置”，然后它将在一列中返回所有与我的主要位置相关的位置ID，无论是直接的，或删除一次或两次或n次。

我可以通过可能发生的第一级链接轻松地做到这一点（请参阅下面的查询），但是一旦我引入了第二级或第三级链接，我就努力寻找除手动更新查询以实现另一级链接之外的另一种方法。

declare  @PrimeLocation int
set @PrimeLocation = 1


Select location_id_1
from [Link Location]
where location_id_1 = @PrimeLocation
or location_id_2 = @PrimeLocation

union

Select location_id_2
from [Link Location]
where location_id_1 = @PrimeLocation
or location_id_2 = @PrimeLocation

该查询显然仅返回“ 1”和“ 5”。但是，如何获取它还返回“
3”以及其他ID，我是否应该在将来向3添加另一个链接，然后再将其从1删除两次？我可以这样做而不必每次都添加到查询中吗？

因此，如果我的“主要位置” = 1（或3或5），则我的结果集应为：

location_id
1
3
5

如果我的“主要位置”是2（或4或6或7），则我的结果集应为：

location_id
2
4
6
7

提前致谢。

阅读 128

2021-05-16

共1个答案

一尘不染

假设id对中的顺序无关紧要，这将产生所需的结果：

-- Sample data.
declare @LinkLocations as Table ( LocationId1 Int, LocationId2 Int );
insert into @LinkLocations ( LocationId1, LocationId2 ) values
  ( 1, 5 ), ( 5, 3 ), ( 2, 6 ), ( 4, 6 ), ( 6, 7 );
select * from @LinkLocations;

-- Search the links.
declare @PrimeLocationId as Int = 1;
with Locations as (
  select @PrimeLocationId as LocationId,
    Cast( '.' + Cast( @PrimeLocationId as VarChar(10) ) + '.' as VarChar(1024) ) as Visited
  union all
  select LL.LocationId1,
    Cast( '.' + Cast( LL.LocationId1 as VarChar(10) ) + L.Visited as VarChar(1024) )
    from @LinkLocations as LL inner join
      Locations as L on L.LocationId = LL.LocationId2
    where L.Visited not like '%.' + Cast( LL.LocationId1 as VarChar(10) ) + '.%'
  union all
  select LocationId2,
    Cast( '.' + Cast( LL.LocationId2 as VarChar(10) ) + L.Visited as VarChar(1024) )
    from @LinkLocations as LL inner join
      Locations as L on L.LocationId = LL.LocationId1
    where L.Visited not like '%.' + Cast( LL.LocationId2 as VarChar(10) ) + '.%' )
  select LocationId -- , Visited
    from Locations
    option ( MaxRecursion 0 );

您可以Visited在最后取消注释select以查看一些内部信息。这将正确处理甚至是退化的情况，例如42, 42将一个id链接到自身的情况。

2021-05-16