链接来自具有公共 ID 的关联实体的所有 ID

2021-09-10 00:00:00 sql tsql sql-server

ERD 只是为了便于可视化:

ERD just for ease to vizualise:

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

Locations from table "Location" can be linked to each other via the associative entity "Link Location"

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

Let's say that there are some current links that look like this:

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

我正在尝试编写一个查询，该查询将返回一个包含所有 ID 的单列，这些 ID 可能相互连接，即使被一个或多个链接删除/远离.因此，1 链接到 5，3 链接到 5.由于 5 是公共 ID，因此删除后 1 也链接到 3.

I am trying to write a query that will return a single column with all IDs that might be connected to each other even if removed/distanced by one or more links. So, 1 is linked to 5 and 3 is linked to 5. Because of the common ID of 5, 1 is also linked to 3 once removed.

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

So, in my query I'd like to be able to decide on a "Prime Location", if you will, and then it will return all location ids, in one column, that are connected to my prime location, be it directly, or once or twice or n times removed.

我可以使用可能发生的 1 级链接轻松完成此操作(请参阅下面的查询)，但是一旦我引入了 2 级或 3 级链接，除了手动更新我的查询以允许另一个链接之外，我正在努力寻找另一种方式链接度.

I can do this easily with the 1st degree of links that might happen (See query below), but once I introduce 2nd or 3rd degree links, I am struggling to see another way other than manually updating my query to allow for another degree of linking.

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 中删除两次?我可以这样做而不必每次都添加到我的查询中吗?

This query obviously only returns "1" and "5". But how do I get it to return "3" as well, and other IDs, should I add another link maybe to 3 in the future that might then be twice removed from 1? And can I do this without having to add to my query every time?

因此，如果我的主要位置"= 1(或 3 或 5)，我的结果集应该是:

So, if my "Prime Location" = 1 (or 3 or 5) my result set should be:

location_id
1
3
5

如果我的主要位置"是 2(或 4、6 或 7)，我的结果集应该是:

And if my "prime location" is 2 (or 4 or 6 or 7) my result set should be:

location_id
2
4
6
7

提前致谢.

推荐答案

假设成对中 id 的顺序没有意义，这将产生预期的结果:

Assuming that the order of id's within pairs has no significance, this will produce the desired results:

-- 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 );

您可以在最后一个 select 中取消注释 Visited 以查看一些内部结构.这甚至可以正确处理像 42, 42 这样将一个 id 链接到自身的退化情况.

You can uncomment Visited in the last select to see some of the internals. This will correctly handle even degenerate cases like 42, 42 that link one id to itself.

相关文章