sql-server - left 将 2 个表连接到一个字段中

Question

我想将下面的 2 个表（h 和 he）加入到主表中，但我只有一个字段 [mpxn] 而不是 2 个 [h.mpxn & he.mpxn]

有没有办法做到这一点？不应该出现在 h 和 he 表中都有结果的情况。

SELECT distinct [Interaction ID]
      ,[BP Number]
      ,he.mpxn
  FROM [DOMCustomers].[Bart].[OpenComplaints] as c
  
  Left join DOMCustomers.Bart.MeterHealth as h on c.[BP Number]=h.[Business Partner]
    Left join DOMCustomers.Bart.ENAMeterHealth as he on c.[BP Number]=he.[Business Partner]

标签： sql-server

Answer 1

你可以UNION在之前把两张桌子放在一起LEFT JOIN

with mpxn_cte([BP Number], mpxn) as (
    select [BP Number], mpxn from DOMCustomers.Bart.MeterHealth
    union
    select [BP Number], mpxn from DOMCustomers.Bart.ENAMeterHealth)
select distinct c.[Interaction ID]
      ,c.[BP Number]
      m.mpxn
from M [DOMCustomers].[Bart].[OpenComplaints] as c
     left jion mpxn_cte m on c.[BP Number]=m.[BP Number];