create table tba(a01 int)
insert into tba select 1
create table tbaa(a01 int,o1 varchar(20),m1 int,p1 decimal(10,2))
insert into tbaa select 1,'a',20,100.2
insert into tbaa select 1,'b',30,54.23
insert into tbaa select 1,'c',38,123.34
create table tbb(b01 int,o1 varchar(20),m1 int,p1 decimal(10,2))
insert into tbb select 1,'a',17,102.5
insert into tbb select 1,'a',3,105.2  --'a'完成验收
insert into tbb select 1,'b',12,58.5
insert into tbb select 1,'b',7,60.2  --'b'未完成验收
--'c'未有验收
go
select a.a01 采购单号,a.o1 产品编号,a.m1 采购数量,a.p1 采购单价,
isnull(sum(b.m1),0)已验收数量,isnull(sum(b.p1*b.m1)/sum(b.m1),0) as 平均验收单价,a.m1-isnull(SUM(b.m1),0) as 未验收数量
from tbaa a left join tbb b on a.a01=b.b01 and a.o1=b.o1
group by a.a01,a.o1,a.m1,a.p1
/*
采购单号        产品编号                 采购数量        采购单价                                    已验收数量       平均验收单价                                  未验收数量
----------- -------------------- ----------- --------------------------------------- ----------- --------------------------------------- -----------
1           a                    20          100.20                                  20          102.905000                              0
1           b                    30          54.23                                   19          59.126315                               11
1           c                    38          123.34                                  0           0.000000                                38
警告: 聚合或其他 SET 操作消除了 Null 值。

(3 行受影响)

*/
go
drop table tba,tbaa,tbb

------解决方案--------------------

SQL code
create table tba(a01 int)
insert into tba select 1
create table tbaa(a01 int,o1 varchar(20),m1 int,p1 decimal(10,2))
insert into tbaa select 1,'a',20,100.2
insert into tbaa select 1,'b',30,54.23
insert into tbaa select 1,'c',38,123.34
create table tbb(b01 int,o1 varchar(20),m1 int,p1 decimal(10,2))
insert into tbb select 1,'a',17,102.5
insert into tbb select 1,'a',3,105.2  --'a'完成验收
insert into tbb select 1,'b',12,58.5
insert into tbb select 1,'b',7,60.2  --'b'未完成验收
--'c'未有验收
go
select a.a01 采购单号,a.o1 产品编号,a.m1 采购数量,a.p1 采购单价,
isnull(sum(b.m1),0)已验收数量,convert(decimal(10,2),isnull(sum(b.p1*b.m1)/sum(b.m1),0)) as 平均验收单价,a.m1-isnull(SUM(b.m1),0) as 未验收数量
from tbaa a left join tbb b on a.a01=b.b01 and a.o1=b.o1
group by a.a01,a.o1,a.m1,a.p1
/*
采购单号    产品编号             采购数量    采购单价            已验收数量  平均验收单价        未验收数量
----------- -------------------- ----------- ------------------- ----------- ------------------- -----------
1           a                    20          100.20              20          102.91              0
1           b                    30          54.23