select tem.id,tem.id2,sum(aa.数量) 购进,sum(bb.数量) 消耗,sum(cc.数量) 销售
from (select distinct ID,ID2 from @购进 distinct union select ID,ID2 from @消耗 union select ID,ID2 from @销售) tem
left join @购进 aa on aa.id=tem.id and aa.id2=tem.id2
left join @消耗 bb on bb.id=tem.id and bb.id2=tem.id2
left join @销售 cc on cc.id=tem.id and cc.id2=tem.id2
group by tem.id,tem.id2
order by tem.id,tem.id2
from (select distinct ID,ID2 from @购进 distinct union select ID,ID2 from @消耗 union select ID,ID2 from @销售) tem
left join @购进 aa on aa.id=tem.id and aa.id2=tem.id2
left join @消耗 bb on bb.id=tem.id and bb.id2=tem.id2
left join @销售 cc on cc.id=tem.id and cc.id2=tem.id2
group by tem.id,tem.id2
order by tem.id,tem.id2
from (select distinct ID,ID2 from @购进 distinct union select ID,ID2 from @消耗 union select distinct ID,ID2 from @销售) tem
left join @购进 aa on aa.id=tem.id and aa.id2=tem.id2
left join @消耗 bb on bb.id=tem.id and bb.id2=tem.id2
left join @销售 cc on cc.id=tem.id and cc.id2=tem.id2
group by tem.id,tem.id2
order by tem.id,tem.id2
from (select distinct ID,ID2 from @购进 distinct union select ID,ID2 from @消耗 union select distinct ID,ID2 from @销售) tem
left join (select id,id2,sum(数量) as 数量 from @购进 group by id,id2) aa on aa.id=tem.id and aa.id2=tem.id2
left join (select id,id2,sum(数量) as 数量 from @消耗 group by id,id2) bb on bb.id=tem.id and bb.id2=tem.id2
left join (select id,id2,sum(数量) as 数量 from @销售 group by id,id2) cc on cc.id=tem.id and cc.id2=tem.id2
group by tem.id,tem.id2
order by tem.id,tem.id2
insert @购进 values ('A' ,'a', 10)
insert @购进 values ('A' ,'a', 20)
insert @购进 values ('C', 'a' , 30)中有ID,ID2重复的直所以会出错
from (select distinct ID,ID2 from @购进 union select distinct ID,ID2 from @消耗 union select distinct ID,ID2 from @销售) tem
left join (select id,id2,sum(数量) as 数量 from @购进 group by id,id2) aa on aa.id=tem.id and aa.id2=tem.id2
left join (select id,id2,sum(数量) as 数量 from @消耗 group by id,id2) bb on bb.id=tem.id and bb.id2=tem.id2
left join (select id,id2,sum(数量) as 数量 from @销售 group by id,id2) cc on cc.id=tem.id and cc.id2=tem.id2
group by tem.id,tem.id2
order by tem.id,tem.id2