有两个表,主表T1,子表T2,他们用一个OrderId关联,T1与T2是一对多关系,现在我要统计一个数据,T1中所有OrderId,统计在T2中对应记录的sum数值,并且,每一个T2 sum出来的结果都要加上T1中的某个字段,请问如何写?我写了一个比较苯的方法,请问有什么可以改进的没?select
T1.businessorgnid,
T2.orderid,
sum(T2.quantity),
max(T1.creationts),
sum(T2.totalprice + T2.totalcharge + T2.totalfacilityfee - T2.totaldiscount) + (select T1.totalshipping from table1 T1 where T1.orderid = T2.orderid)
from table1 T1, table2 T2
where T1.orderid=T2.orderid and T1.instanceid=T2.instanceid
group by T1.businessorgnid, T2.orderid
T1.businessorgnid,
T2.orderid,
sum(T2.quantity),
max(T1.creationts),
sum(T2.totalprice + T2.totalcharge + T2.totalfacilityfee - T2.totaldiscount) + (select T1.totalshipping from table1 T1 where T1.orderid = T2.orderid)
from table1 T1, table2 T2
where T1.orderid=T2.orderid and T1.instanceid=T2.instanceid
group by T1.businessorgnid, T2.orderid
T1.businessorgnid,T2.orderid,
sum(T2.quantity),max(T1.creationts),
sum(T2.totalprice + T2.totalcharge + T2.totalfacilityfee -
T2.totaldiscount) + sum(T1.totalshipping)
from table1 T1, table2 T2
where T1.orderid=T2.orderid and T1.instanceid=T2.instanceid
group by T1.businessorgnid, T2.orderid