我想根據從第一個表中獲取的 ID 加入多個表。
下面是一個例子：
地點：
| ID | 城市 ID |
|---|---|
| 1 | 92418 |
職業運動隊：
| 城市 ID | Professional_Team_Count | Participant_Count |
|---|---|---|
| 92418 | 3 | 75 |
非職業運動隊表：
| 城市 ID | Non_Pro_Team_Count | Participant_Count |
|---|---|---|
| 92418 | 25 | 750 |
我試圖回傳一個看起來像這個結果表的表
| ID | 城市 ID | Professional_Team_Count | Non_Pro_Team_Count | Participant_Count |
|---|---|---|---|---|
| 1 | 92418 | 3 | 空值 | 75 |
| 1 | 92418 | 空值 | 25 | 750 |
uj5u.com熱心網友回復：
select loc.Id, loc.locationId, s.ProductId, s.SerialNumber, null as Quantity
from Location loc
inner join Serialized s on loc.LocationId = s.locationId
where loc.Id = 1
union all
select loc.Id, loc.locationId, ns.ProductId, null, ns.Quantity
from Location loc
inner join nonSerialized ns on loc.LocationId = ns.locationId
where loc.Id = 1
會做的。您需要從 2 組資料中合并資料。
uj5u.com熱心網友回復：
你需要做一個聯合，從兩個表中獲取行：
select loc.ID, loc.LocationID, s.ProductID, s.SerialNumber,
null as Quantity
from Location loc
join Serialized s on s.ID = loc.ID
union all
select loc.ID, loc.LocationID, ns.ProductID, null as SerialNumber,
ns.Quantity
from Location loc
join NonSerialized ns on ns.ID = loc.ID
uj5u.com熱心網友回復：
為避免Location多次加入，您可以將UNION ALL派生表放在里面
select
loc.Id,
loc.locationId,
s.ProductId,
s.SerialNumber,
s.Quantity
from Location loc
inner join (
select
s.locationId,
s.SerialNumber,
null as Quantity
from Serialized s
union all
select
ns.locationId,
null,
ns.Quantity
from nonSerialized ns
) s on loc.LocationId = s.locationId
where loc.Id = 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/384921.html
標籤：sql sql-server 查询语句 加入