問題描述
ERD 只是為了便于可視化:
ERD just for ease to vizualise:
Location"表中的位置可以通過關(guān)聯(lián)實(shí)體Link Location"相互鏈接
Locations from table "Location" can be linked to each other via the associative entity "Link Location"
假設(shè)有一些當(dāng)前鏈接如下所示:
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
我正在嘗試編寫一個(gè)查詢，該查詢將返回一個(gè)包含所有 ID 的單列，這些 ID 可能相互連接，即使被一個(gè)或多個(gè)鏈接刪除/遠(yuǎn)離.因此，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.
因此，在我的查詢中，如果您愿意，我希望能夠確定一個(gè)主要位置"，然后它將在一列中返回所有位置 ID，這些 ID 與我的主要位置相關(guān)聯(lián)，是直接刪除，或刪除一兩次或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.
我可以使用可能發(fā)生的 1 級(jí)鏈接輕松完成此操作(請(qǐng)參閱下面的查詢)，但是一旦我引入了 2 級(jí)或 3 級(jí)鏈接，除了手動(dòng)更新我的查詢以允許另一個(gè)鏈接之外，我正在努力尋找另一種方式鏈接度.
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
這個(gè)查詢顯然只返回1"和5".但是我如何讓它也返回3"和其他 ID，我是否應(yīng)該在將來添加另一個(gè)鏈接到 3，然后可能會(huì)從 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)，我的結(jié)果集應(yīng)該是:
So, if my "Prime Location" = 1 (or 3 or 5) my result set should be:
location_id
1
3
5
如果我的主要位置"是 2(或 4、6 或 7)，我的結(jié)果集應(yīng)該是:
And if my "prime location" is 2 (or 4 or 6 or 7) my result set should be:
location_id
2
4
6
7
提前致謝.
推薦答案
假設(shè)成對(duì)中 id 的順序沒有意義，這將產(chǎn)生預(yù)期的結(jié)果:
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 );
您可以在最后一個(gè) select 中取消注釋 Visited 以查看一些內(nèi)部結(jié)構(gòu).這甚至可以正確處理像 42, 42 這樣將一個(gè) 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.
這篇關(guān)于鏈接來自具有公共 ID 的關(guān)聯(lián)實(shí)體的所有 ID的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！