如何连接具有两个公共列值的两个表并将其余列合并

I'd like to combine Table A and Table B at the link below and end up with Table C. What is the best way to do this in SQL? I've thought about creating a composite key between the tables for LedgerID + Year doing an inner join and then unioning the left and right only data. I'm also curious how to avoid duplicating values across rows like Balance = 50.00 ending up in rows for Tires and Windshield.

我想在下面的链接中结合表A和表B,最后得到表C.在SQL中执行此操作的最佳方法是什么?我考虑过在LedgerID + Year的表之间创建一个复合键,进行内连接,然后联合左右数据。我也很好奇如何避免跨行重复值,例如Balance = 50.00,最后是Tires和Windshield的行。

4 个解决方案

#1

Try a full outer join, joining on LedgerID and Year, using coalesce to show Table B's LedgerID/Year when Table A's is NULL:

尝试完全外部联接,加入LedgerID和Year,使用coalesce在表A为NULL时显示表B的LedgerID / Year:

SELECT 
COALESCE(A.LedgerID, B.LedgerID) as LedgerID,
COALESCE(A.Year, B.Year) as Year,
A.Title,
A.Payment,
B.Balance
FROM "Table A" AS A
FULL OUTER JOIN "Table B" AS B ON (A.LedgerID=B.LedgerID AND A.Year=B.Year)

#2

--Please try this Query. Since you have only reference LedgerId and Year, the balance will show 50 for both Tires & Windshield

- 请尝试此查询。由于您只引用了LedgerId和Year,因此轮胎和挡风玻璃的余额将显示为50

; with cte_Ledger (LedgerId, [year])
AS
(
Select DISTINCT LedgerId, [year]
From tableA
UNION
Select DISTINCT LedgerId, [year]
From tableB
)
select t.LedgerId
        , t.[year]
        , t1.Title
        , T1.Payments
        , t2.Balance
FROM cte_Ledger t
left join tableA t1 on t.LedgerId = t1.LedgerId  and t.[year] = t1.[year]
left join tableB  t2 on t2.LedgerId = t.LedgerId and t2.[year] = t.[year]

#3

I think so, Above Queries will not help to get expected result. some misunderstanding is with requirement. For ledgerid = 22 and Year = 2017, have 2 records in table-A and 1 with Table-B. But in expecting result, Balance 50(Record of table-B) is exists with matched first row of Table-A only. As per above all logic it will be with 2 Records where ledgerid = 22, Year = 2017 and Title with "Tires" & "Windshield".

我想是这样,Above Queries无助于获得预期的结果。一些误解是有要求的。对于ledgerid = 22和Year = 2017,表-A中有2条记录,表-B中有1条记录。但是在预期结果中,Balance 50(表-B的记录)仅存在与表-A的匹配的第一行。按照上述所有逻辑,它将有2个记录,其中ledgerid = 22,年= 2017,标题为“轮胎”和“挡风玻璃”。

If required same result as mentioned then need to use recursive CTE or ranking function with order of ID column.

如果需要与上述相同的结果,则需要使用具有ID列顺序的递归CTE或排序函数。

#4

Here is my solution after I loaded the tables, another nested case statement may be need to format out the zero on Ledger 24.

这是我加载表后的解决方案,另一个嵌套的case语句可能需要格式化Ledger 24上的零。

Select 
[LedgerID],
[Year],
Case when PayRank = 1 then Title else ''  end as Title,
Case when PayRank = 1 then convert(varchar(20),Payments) else '' end as 
Payments,
Case when BalRank = 1 then convert(varchar(20),Balance) else '' end as 
Balance
from(

SELECT 
  B.[LedgerID]
  ,B.[Year]
  ,Rank()Over(Partition by B.LedgerID,Payments order by 
   B.LedgerID,B.Year,Title) as PayRank
  ,isnull([Title],'') as Title
  ,isnull([Payments],0) as Payments
  ,Rank()Over(Partition by B.LedgerID,B.Year order by 
   B.LedgerID,B.Year,Payments) as BalRank
  ,Balance
   FROM [TableB] B
   left outer join [TableA] A
   on A.LedgerID = B.LedgerID 
    ) Query
   order by LedgerID,Year

enter image description here

在此处输入图像描述

#1