A(a):一条记录,01
B(a,b,idx_b):两条记录,(01,10,001),(01,20,002)
C(a,c,idx_c):一条记录,(01,5,101)B,C两张表的惟一索引字段idx_b,idx_c没什么关系。现在我想将B,C两张表的b,c字段分别以a字段group求和,得到表D(a,sum(b),sum(c)),D中的记录是,(01,30,5)。我写了句select a,sum(b),sum(c)
from A a1,B b1,C c1
where b1.a = a1.a
and c1.a = a1.a
group by a
可得到的是记录,(01,30,10)求正解,紧急!!谢谢大虾们!!感激不尽!!
B(a,b,idx_b):两条记录,(01,10,001),(01,20,002)
C(a,c,idx_c):一条记录,(01,5,101)B,C两张表的惟一索引字段idx_b,idx_c没什么关系。现在我想将B,C两张表的b,c字段分别以a字段group求和,得到表D(a,sum(b),sum(c)),D中的记录是,(01,30,5)。我写了句select a,sum(b),sum(c)
from A a1,B b1,C c1
where b1.a = a1.a
and c1.a = a1.a
group by a
可得到的是记录,(01,30,10)求正解,紧急!!谢谢大虾们!!感激不尽!!
不过where b1.a = a1.a
and c1.a = a1.a
是否可以为b1.a=c1.a
group by a.a
from B b1,C c1
where b1.a = c1.a
group by b1.a
from (select a,sum(b) bamt from B) b1,(select a,sum(c) camt from C) c1, A a1
where b1.a = a1.a
and c1.a = a1.a
2 from a, (select cola, sum(colb)as colb from b group by cola)b,
3 (select cola, sum(colc) as colc from c group by cola)c
4 where a.cola = b.cola and a.cola = c.cola
5 /COLA COLB COLC
------------ ---------- ----------
01 30 5
FROM
(
SELECT a1.a a,b1.b,0 c
FROM A a1
INNER JOIN B b1
ON a1.A=c1.A
UNION
SELECT a1.a a,0 b,c1.C c
FROM A a1
INNER JOIN C c1
ON a1.A=c1.A
)
GROUP BY a
--我也寫了一個,不過效率可能差了點。請高手看看有沒有更好優化效率的方法。
只有sxfwang的方法是可以的,但我还想对sum(b),sum(c)进行横向汇总,例如得到记录(01,35,30,5)这怎么求和啊?不好意思,也许问题较低级,但请赐教,感恩流涕T_T,
另外怎么给分啊?.....
select A.a,
(select sum(b) from b B where B.a=A.a),
sum(q.c)
from a A,b B,c C
where A.a=C.a
group by 1,2
但我接下来,还要横向汇总求,sum(b)+sum(c),又没辄了
informix板块人气好差,才到这边来求方
select t.a,t.sumb,v.sumc
from
(select a,sum(b) sumb from B group by a) t
(select a,sum(c) sumc from C group by a) v
where t.a=v.a