oracle数据库,使用PL/SQL进行操作,3表结构如下:
表A
字段aa
字段bb
字段cc
表B
字段aa
字段kk
字段ww
字段xx
字段yy 表C
字段kk
字段mm B.aa有重复值,A.aa无重复值且(B.aa in A.aa),(B.kk in C.kk),现在要根据kk的范围对表B中的ww,xx和yy求和,然后group by B.aa,显示的时候不显示B.aa而要显示其对应的A.bb,最终显示结果应该是A.bb,sum(B.ww),sum(B.xx),sum(B.yy),具体的SQL语句该怎么写呢?
表A
字段aa
字段bb
字段cc
表B
字段aa
字段kk
字段ww
字段xx
字段yy 表C
字段kk
字段mm B.aa有重复值,A.aa无重复值且(B.aa in A.aa),(B.kk in C.kk),现在要根据kk的范围对表B中的ww,xx和yy求和,然后group by B.aa,显示的时候不显示B.aa而要显示其对应的A.bb,最终显示结果应该是A.bb,sum(B.ww),sum(B.xx),sum(B.yy),具体的SQL语句该怎么写呢?
from a,b
where a.aa = b.aa
and b.kk = 'xxxx'
group by a.bb
sum(B.ww),sum(B.xx),sum(B.yy)
from b
group by b.aa
sum(B.ww),sum(B.xx),sum(B.yy)
from b
group by b.aa
from a,b
where a.aa = b.aa
and b.kk in (list)
group by a.bb
sum(B.ww),sum(B.xx),sum(B.yy)
from b
group by b.aa 这种方式使用走嵌套循环,如果没有索引会比较慢select a.bb,sum(B.ww),sum(B.xx),sum(B.yy)
from a,b
where a.aa = b.aa
and b.kk in (list)
group by a.bb 可以走hash联接 数据量大的话比较快
select
a.bb,
d.m,
d.n,
d.l
from
(
select
b.aa aa,
sum(B.ww) m,
sum(B.xx) n,
sum(B.yy) l
from
b
where
b.kk in (list)
group by b.aa
) d,
a
where
a.aa = d.aa