两个表关联，字段部分相等
如题：要求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
select 关联表 部分相等
[解决办法]
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
[解决办法]
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
