统计问题
表A(ParentID,KindID,Kind)//关联B,字段KindID
表B(KindID,MaterialID,Material,Type,Units)//关联P2,C2,字段MaterialID

表P1(DH,Department,RDate,Type,keeper,Sender)//主表，关联P2,字段DH
表P2(DH,MaterialID,price,Num,total)//从表

表C1(DH,Department,CDate,Type,keeper,Geter)//主表，关联C2,字段DH
表C2(DH,MaterialID,price,Num,total)//从表

现在想统计得到结果字段格式如下：

A.Kind,B.MaterialID,B.Material,B.Type,B.Units,RNum=Sum(P2.Num),
RTotal=Sum(P2.Total),CNum=Sum(C2.Num),CTotal=Sum(C2.Total)
 
说明:P2,C2是明细表，可能存在相同的记录，现在想统计出B表中的每个MaterialID总的C2.Num与P2.Num

如
A:  
ParentID,   KindID,     Kind
                    GC               钢材      
B:
KindID,     MaterialID,     Material   ,     Type,     Units
GC                 GC01                 螺纹钢             ￠12           吨


P2://主表就不写了
DH,       MaterialID,     price,     Num,     total
1             GC01                   3600       10           36000

C2://主表就不写了
DH,       MaterialID,     price,     Num,     total
1             GC01                   3600         4         14400
2             GC01                   3600         3         10800

统计结果
Kind   MaterialID   Material   Type   Units   RNum,   RTotal,   Cnum,   CTotal
钢材       GC01           螺纹钢       ￠12     吨         10         36000       7         25200

当前，如果前面主表的日期或者部门不一样，假如对这些条件做限制的话
得到的结果也应该不一样。




------解决方案--------------------
SELECT
A.Kind,B.MaterialID,B.Material,B.Type,B.Units,
RNum= (SELECT Sum(Num) FROM P2 WHERE MaterialID = B.MaterialID),
RTotal = (SELECT Sum(Num) FROM P2 WHERE MaterialID = B.MaterialID),
CNum = (SELECT Sum(Num) FROM C2 WHERE MaterialID = B.MaterialID),
CTotal = (SELECT Sum(Total) FROM C2 WHERE MaterialID = B.MaterialID)
FROM A LEFT OUTER JOIN B ON A.KindID = B.KindID
------解决方案--------------------
select T.Kind,T.MaterialID,T.Material,T.Type,T.Units,
RNum=Sum(P2.Num),RTotal=Sum(P2.Total),CNum=Sum(C2.Num),CTotal=Sum(C2.Total)
from
(
select A.Kind,B.MaterialID,B.Material,B.Type,B.Units
from 表A A,表B B
where A.KindID=B.KindID
) T
left join
P2 on T.MaterialID=P2.MaterialID
left join
C2 on T.MaterialID=C2.MaterialID
group by T.Kind,T.MaterialID,T.Material,T.Type,T.Units