从两个不同的表中查找sum()并根据条件将它们连接起来

Question

我有两个表，

表1:

ID   Amount  Date
------------------
123  500.00  02-Sep-2020
123  240.00  02-Sep-2020
124  200.50  02-Sep-2020
125  150.70  03-Sep-2020
123  480.80  03-Sep-2020

表2

ID   Settled_Amount  Date
-------------------------------
123    150.25       02-Sep-2020
124    200.00       03-Sep-2020
125    100.40       03-Sep-2020

我想将特定ID组的表1的Amount列和表2的settled _aunt列与Date列相加。

所以我的结果是ID=123:

Sum(Amount) Sum(Settled_amount) Date
------------------------------------------
740.00       150.25             02-Sep-2020
480.80                          03-Sep-2020

Answer 1

您可以使用union all和group by。对于所有ids：

select id, date, sum(amount), sum(amount_settled)
from ((select id, date, amount, null as amount_settled
from table1
) union all
(select id, date, null as amount, amount_settled
from table2
) 
) t
group by id, date
order by date;

可以使用外部查询中的where子句筛选特定的id。

Answer 2

另一种不用像Gordon那样进行子删除即可编写的方法。

declare @table1 table  (id int, amount numeric(18,2), Dates Date)
Insert into @table1
values 
(123 ,500.00  ,'02-Sep-2020'),
(123 ,240.00  ,'02-Sep-2020'),
(124 ,200.50  ,'02-Sep-2020'),
(125 ,150.70  ,'03-Sep-2020'),
(123 ,480.80  ,'03-Sep-2020')
declare @table2 table  (id int, Settled_Amount numeric(18,2), Dates Date)
insert into @table2
values 
(123   , 150.25      ,'02-Sep-2020'),
(124   , 200.00      ,'03-Sep-2020'),
(125   , 100.40      ,'03-Sep-2020');
with Table1 as (
select sum(amount) as Amount,ID,Dates from @table1
group by ID,Dates
)
,
Table2 as (
Select sum(Settled_amount) as Settled_amount, ID,Dates from @table2
group by ID,Dates
)
select Amount,Settled_amount,a.Dates,a.ID from Table1 a left join table2 b on a.id = b.id and a.Dates = b.Dates
where a.id=123