久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

鏈接來自具有公共 ID 的關聯實體的所有 ID

Link all IDs from associative entity that have common ID(鏈接來自具有公共 ID 的關聯實體的所有 ID)
本文介紹了鏈接來自具有公共 ID 的關聯實體的所有 ID的處理方法,對大家解決問題具有一定的參考價值,需要的朋友們下面隨著小編來一起學習吧!

問題描述

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.

這篇關于鏈接來自具有公共 ID 的關聯實體的所有 ID的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題,如果有圖片或者內容侵犯了您的權益,請聯系我們刪除處理,感謝您的支持!

相關文檔推薦

Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(將每個子標記轉換為具有多個分隔符的單列-SQL Server (3))
How can I create a view from more than one table?(如何從多個表創建視圖?)
Create calculated value based on calculated value inside previous row(根據前一行內的計算值創建計算值)
How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何將表格的前兩列堆疊成一列,但也僅將第三列與第一列配對?) - IT屋-程序員軟件開發技
Recursive t-sql query(遞歸 t-sql 查詢)
Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(將月份名稱轉換為日期/月份編號(問題和答案的組合))
主站蜘蛛池模板: 国产一区二区三区精品久久久 | 国产伦精品一区二区三区照片91 | 国产精品国产a | 狠狠做六月爱婷婷综合aⅴ 国产精品视频网 | 亚洲视屏 | 中文字幕亚洲专区 | 久久r精品| 影音先锋中文字幕在线观看 | 久久久久久www | 久久久久中文字幕 | 成人在线免费观看 | 99在线国产 | 中文字幕亚洲区一区二 | 无码国模国产在线观看 | 不卡视频一区二区三区 | 日韩欧美在线不卡 | 成人免费一区二区三区视频网站 | 欧美亚洲网站 | 亚洲精品一区二区三区免 | 九九热国产精品视频 | 久久久久国产一区二区三区 | 亚洲国内精品 | 日韩免费视频一区二区 | 亚洲视频中文字幕 | 国产欧美精品区一区二区三区 | 国产成人午夜精品影院游乐网 | 狠狠ri | 成人精品在线观看 | 国产成人精品一区二区三区视频 | 日本视频免费观看 | 狠狠热视频 | 国产综合久久 | 国产乱码精品一品二品 | 久久亚洲精品视频 | 亚洲精品电影网在线观看 | 亚洲成a人片 | 亚洲精品女人久久久 | 五月婷亚洲 | 色综合久久久久 | 中文字幕日韩一区 | 亚洲免费观看视频 |