SQL SERVER可以这么写:
select a.pName,a.Spec,a.Unit,sum(b.Qty) as BTotal,sum(b.Qty) as CTotal,sum(b.Qty) as Dtotal,sum(b.Qty) as Etotal, isnull(sum(b.Qty),0)+isnull(sum(c.Qty),0)+isnull(sum(d.Qty),0)+isnull(sum(e.Qty),0) as Total
from A,B,C,D,E
where a.pName*=b.pName and a.Spec*=b.Spec
and a.pName*=c.pName and a.Spec*=c.Spec
and a.pName*=d.pName and a.Spec*=d.Spec
and a.pName*=e.pName and a.Spec*=e.Spec
and (
exists (select * from b where pName=a.pName and Spec=a.Spec )
or exists (select * from c where pName=a.pName and Spec=a.Spec )
or exists (select * from d where pName=a.pName and Spec=a.Spec )
or exists (select * from e where pName=a.pName and Spec=a.Spec )
)
select a.pName,a.Spec,a.Unit,sum(b.Qty) as BTotal,sum(b.Qty) as CTotal,sum(b.Qty) as Dtotal,sum(b.Qty) as Etotal, isnull(sum(b.Qty),0)+isnull(sum(c.Qty),0)+isnull(sum(d.Qty),0)+isnull(sum(e.Qty),0) as Total
from A,B,C,D,E
where a.pName*=b.pName and a.Spec*=b.Spec
and a.pName*=c.pName and a.Spec*=c.Spec
and a.pName*=d.pName and a.Spec*=d.Spec
and a.pName*=e.pName and a.Spec*=e.Spec
and (
exists (select * from b where pName=a.pName and Spec=a.Spec )
or exists (select * from c where pName=a.pName and Spec=a.Spec )
or exists (select * from d where pName=a.pName and Spec=a.Spec )
or exists (select * from e where pName=a.pName and Spec=a.Spec )
)
from a,
(select pName,Spec,sum(nvl(qty,0)) btotal from b) b
(select pName,Spec,sum(nvl(qty,0)) ctotal from b) c
(select pName,Spec,sum(nvl(qty,0)) dtotal from b) d
(select pName,Spec,sum(nvl(qty,0)) etotal from b) e
where a.pName = b.pName
and a.Spec = b.Spec
and a.pName = c.pName
and a.Spec = c.Spec
and a.pName = d.pName
and a.Spec = d.Spec
and a.pName = e.pName
and a.Spec = e.Spec
sum(b.qty) bTotal,sum(c.qty) Total,
sum(d.qty) Total,sum(e.qty) Total,
nvl(sum(b.qty),0) + nvl(sum(c.qty),0) + nvl(sum(d.qty),0) + nvl(sum(e.qty),0) Total
from a,b,c,d,e
where a.pName = b.pName (+)
and a.Spec = b.Spec (+)
and a.pName = c.pName (+)
and a.Spec = c.Spec (+)
and a.pName = d.pName (+)
and a.Spec = d.Spec (+)
and a.pName = e.pName (+)
and a.Spec = e.Spec (+)
and ( b.pName is not null
or c.pName is not null
or d.pName is not null
or e.pName is not null)
group by a.pName, a.Spec, a.Unit;
nvl to isnull
----------------------
select pname,spec,unit,btotal,ctotal,dtotal,etotal,btoal+ctotal+dtotal+etotal as total from
(
select a.pname,a.spec,a.unit,
nvl((select sum(qty) from b where pname=a.pname and spec=a.spec),0) as btotal,
nvl((select sum(qty) from c where pname=a.pname and spec=a.spec),0) as ctotal,
nvl((select sum(qty) from d where pname=a.pname and spec=a.spec),0) as dtotal,
nvl((select sum(qty) from e where pname=a.pname and spec=a.spec),0) as etotal
from a
) tbl
@begindate datetime,@enddate datetime
as
select pName,spec,sum(qty) as BTotal,0 as cTotal,0 as dTotal,0 as eTotal
into #temp
from b where date>@begindate and date<=@enddate
group by pName,spec
union all
select pName,spec,0 as bTotal,sum(qty) as cTotal,0 as dTotal,0 as eTotal
from c where date>@begindate and date<=@enddate
group by pName,spec
union all
select pName,spec,0 as bTotal,0 as cTotal,sum(qty) as dTotal,0 as eTotal
from d where date>@begindate and date<=@enddate
group by pName,spec
union all
select pName,spec,0 as bTotal,0 as cTotal,0 as dTotal,sum(qty) as eTotal
from e where date>@begindate and date<=@enddate
group by pName,specselect pName,spec
,sum(bTotal) as bTotal
,sum(cTotal) as cTotal
,sum(dTotal) as dTotal
,sum(eTotal) as eTotal
into #temp1
from #temp
group by pName,specselect a.pName,a.spec,a.Unit,b.BTotal,b.CTotal,b.Dtotal,b.Etotal
,sum(b.BTotal+b.CTotal+b.Dtotal+b.Etotal) as Total
from #temp1 b join a on a.pName=b.pName and a.spec=b.spec
drop table #temp
drop table #temp0
select a.pNmae, a.Spec, a.Unit
sum(b.qty) bTotal,sum(c.qty) cTotal,
sum(d.qty) dTotal,sum(e.qty) eTotal,
nvl(sum(b.qty),0) + nvl(sum(c.qty),0) + nvl(sum(d.qty),0) +
nvl(sum(e.qty),0) Total
from a,b,c,d,e
where a.pName = b.pName (+)
and a.Spec = b.Spec (+)
and a.pName = c.pName (+)
and a.Spec = c.Spec (+)
and a.pName = d.pName (+)
and a.Spec = d.Spec (+)
and a.pName = e.pName (+)
and a.Spec = e.Spec (+)
group by a.pName, a.Spec, a.Unit;
不过如果A表中能够建立一个主键最好,那就不用写那么多AND了。