我在 T-SQL 中有這種情況。
表 1 - 產品（id、名稱、StartLocationId、FinalLocationId）。
StartLocationId 和 FinalLocationId 是 Location 的識別符號。
Id Product StartLocationId FinalLocationId
1 Porsche 1 2
2 Bentley 2 3
3 Maseratti 3 1
表 2 - 位置（ID、名稱）
Id Name
1 Garage Motor
2 Firestone
3 Michelin
我需要得到這些：
Product NameStartLocation NameFinalLocation
Porsche Garage Motor Firestone
Bentley Firestone Michelin
Maseratti Michelin Firestone
我試過：
Select
Product.Name
,(select Location.Name
from Product inner join
Location ON Product.StartLocationId = Location.Id)
,(select Location.Name
from Product inner join
Location ON Product.FinalLocationId = Location.Id)
from Product
但是在子查詢中不可能有多個值，并且我不能使用 top 和 order by 來獲取所需的表。
uj5u.com熱心網友回復：
嘗試如下使用加入
select p.Product,ls.name as startlocation,
lf.name as finallocation
from product p
left join Location ls on p.StartLocationId=ls.id
left join Location lf on p.FinalLocationId=lf.id
uj5u.com熱心網友回復：
您可以嘗試使用兩個相關的子查詢，嘗試如下：
Select p.Name
,(select l.Name from Location l where l.Id = p.StartLocationId) as NameStartLocation
,(select l.Name from Location l where l.Id = p.FinalLocationId) as NameFinalLocation
from Product p
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/431799.html
上一篇：在SQL中將2列計數為1