MSSQL：左連接三個表中的多個列-有解無憂

我在 MS SQL Server 中有三個表：

Tenants:
TenantID int,
LastName varchar(50),
FirstName varchar(50)

Intervals:
Value int,
Name varchar(50)

Leases:
LeaseID int,
Interval int,
StartDate date,
EndDate date,
Tenant1 int,
Tenant2 int,
Tenant3 int,
Tenant4 int

Intervals 表有三行：[1,'Monthly']、[2,'BiWeekly'] 和 [3,'Weekly']

我需要一個具有以下結果集的查詢：

LID Type      Start         End      Tenant1       Tenant2        Tenant3       Tenant4
21  Monthly  12/15/2019  12/15/2020  Sean Murphy   Audrey Moore   Randy Davis
32  Weekly   06/01/2018              Pete Higgins  Kathy Higgins  
35  Monthly  08/01/2019  10/31/2020  Andy Stacke   Valerie Stacke
44  Monthly  01/01/2021              Pete O'Toole  Martha White

現在暫且不談名字姓氏列，我的第一次嘗試成功使用了兩個左連接：

select L.LeaseID, I.Name as Type, L.StartDate, L.EndDate, T.LastName as Tenant1 
from Leases L 
left join Intervaltypes I on I.value=L.Interval 
left join Tenants T on T.TID = L.Tenant1

并回傳：

LID Type      Start         End      Tenant1 
21  Monthly  12/15/2019  12/15/2020  Murphy  
32  Weekly   06/01/2018              Higgins
35  Monthly  08/01/2019  10/31/2020  Stacke
44  Monthly  01/01/2021              O'Toole

一個好的開始，但我似乎無法從租戶表中獲得最多 4 個唯一名稱，我想在租戶列中組合（名字姓氏）。

我為同一個表（租戶）嘗試了多個左連接，并在一個連接上嘗試了 ORing 條件；他們都在直覺上似乎是錯誤的。

select L.lid,L.ruid, I.Name, T.LastName as Tenant1, T.LastName as Tenant2 from Leases L 
left join Intervaltypes I on I.value=L.Interval 
left join Tenants T on T.TID = L.Ten1
left join Tenants T on T.TID = L.Ten2

select L.lid,L.ruid, I.Name, T.LastName as Tenant1, T.Lastname as Tenant2 from Leases L 
left join Intervaltypes I on I.value=L.Interval 
left join Tenants T on T.TID = L.Ten1 or T.TID = L.Ten2

但是這兩個列在兩列中回傳了相同的姓氏。

uj5u.com熱心網友回復：

在這里沒有網路作業，但我相信你想要這樣的東西：

select 
    L.LeaseID AS 'LID', 
    I.Name AS 'Type', 
    L.StartDate AS 'Start', 
    L.EndDate AS 'End', 
    CONCAT(T1.FirstName, ' ', T1.LastName) AS 'Tenant1', 
    CONCAT(T2.FirstName, ' ', T2.LastName) AS 'Tenant2', 
    CONCAT(T3.FirstName, ' ', T3.LastName) AS 'Tenant3', 
    CONCAT(T4.FirstName, ' ', T4.LastName) AS 'Tenant4'
from Leases L
left join Intervals I ON L.Interval = I.Value
left join Tenants T1 ON L.Tenant1 = T1.TenantID
left join Tenants T2 ON L.Tenant2 = T2.TenantID
left join Tenants T3 ON L.Tenant3 = T3.TenantID
left join Tenants T4 ON L.Tenant4 = T4.TenantID

uj5u.com熱心網友回復：

試試這個

select
  L.lid
  ,L.ruid
  , I.Name
  , T1.LastName as Tenant1
  , T2.LastName as Tenant2 
from Leases L 
left join Intervaltypes I on I.value=L.Interval 
left join Tenants T1 on T1.TID = L.Ten1
left join Tenants T2 on T2.TID = L.Ten2

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/399470.html

標籤：sql 左连接

上一篇：SQLServer合并重復條目

下一篇：定制訂單