两个表关联，字段部分相等

[ljr_aa]

如题：要求FactoryNo、List_id相等，表#A有输入SizeID的，按相同的SizeID取汇总，没有的按FactoryNo、List_id相等取汇总。

if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A(FactoryNo varchar(10),List_id varchar(10), SizeID varchar(10),Quantity int)
insert into #A
select '8','111','',1 union all
select '8','111','',2 union all
select '8','111','',3 union all
select '8','111','',4 union all
select '8','112','S',2 union all
select '8','112','M',3 union all
select '8','112','',4 union all
select '8','112','XL',5

if object_id('tempdb.dbo.#A') is not null drop table #B
create table #B(FactoryNo varchar(10),List_id varchar(10), SizeID varchar(10),Quantity int)
insert into #B
select '8','111','S',2 union all
select '8','111','M',3 union all
select '8','111','L',4 union all
select '8','111','XL',5 union all
select '8','112','S',6 union all
select '8','112','M',7 union all
select '8','112','L',8 union all
select '8','112','XL',9

结果显示如下：
FactoryNo List_id SizeID Quantity TotQty
---------- ---------- ---------- ----------- -----------
8 111 1 14
8 111 2 14
8 111 3 14
8 111 4 14
8 112 S 2 6
8 112 M 3 7
8 112 4 30
8 112 XL 5 9

[Vidor]

select a.*, b.Quantity from #A a inner join ( select * from #B union all select FactoryNo, List_id, '', sum(Quantity) from #B group by FactoryNo, List_id ) b on a.FactoryNo=b.FactoryNo and a.List_id=b.List_id and a.SizeID=b.SizeID

[MrYangkang]

select a.factoryno,a.list_id,a.sizeid,a.Quantity,b.Quantity as totqty
from a inner join b on a.factoryno =b.factoryno 
and a.list_id = b.list_id and a.sizeid = b.sizeid
union all
select a.factoryno,a.list_id,a.sizeid,a.Quantity,sum(b.Quantity) as totqty
from a inner join b on a.factoryno =b.factoryno 
and a.list_id = b.list_id and (a.sizeid is null or a.sizeid='')
group by a.factoryno,a.list_id,a.sizeid,a.Quantity