22,301
社区成员




select 供应商,金额=sum(JFJe-DFJe)
from (select 供应商,JFJe=总金额,DFJe=退货金额 from tablea
union all
select 应商,JFJe=欠款金额,DFJe=0 from TableB
) as t
group by 供应商
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([供应商] nvarchar(22),[类别] nvarchar(22),[总金额] int,[退货金额] int)
Insert #A
select N'AA',N'内衣',200,0 union all
select N'AA',N'内裤',300,100 union all
select N'BB',N'袜子',500,200 union all
select N'BB',N'秋衣',600,400
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([供应商] nvarchar(22),[欠款金额] int)
Insert #B
select N'AA',400 union all
select N'AA',100 union all
select N'BB',0 union all
select N'BB',500
Go
--测试数据结束
SELECT t1.供应商 ,
t1.金额 + t2.金额 AS 总欠金额
FROM ( SELECT 供应商 ,
SUM(总金额 - 退货金额) AS 金额
FROM #A
GROUP BY 供应商
) t1
JOIN ( SELECT 供应商 ,
SUM(欠款金额) AS 金额
FROM #B
GROUP BY 供应商
) t2 ON t2.供应商 = t1.供应商
select a1.供应商, a1.总金额-a1.退货金额+b1.欠款金额 from (
select 供应商,sum(总金额) as 总金额,sum (退货金额) as 退货金额 from a group by 供应商) as a1 inner join
(select 供应商,sum(欠款金额) as 欠款金额 from b group by 供应商) as b1 on a1.供应商=b1.供应商
大概这样没有验证过你可以验证下