请教 大数据量+多表查询

Question

select a.neme,b.scores+c.scores+d.scores+e.scores+f.scores+g.scores from students a,yuwen_talbe b,shuxue_table c,yingyu_table d,zhengzhi_table e,wuli_table f,huaxue_table g where a.id=b.id and a.id=c.id and a.id=d.id and a.id=e.id and a.id=f.id and a.id=g.id;

Answer 1

SQL> select * from student;ID         NAME
---------- ----------
0001       zhangsan
0002       lisi
0003       wangwu
0004       zhaoliu
0005       houqi
0006       maba
0007       noname已选择7行。SQL> select * from yw;ID              SCORE
---------- ----------
0001              100
0002               90
0003               80
0004               70
0005               60
0006               50已选择6行。SQL> select * from sx;ID              SCORE
---------- ----------
0001              100
0002               90
0003               80
0004               70SQL> select * from yy;ID              SCORE
---------- ----------
0001              100
0002               90
0003               80
0006               50SQL> select a.name,nvl(b.score,0)+nvl(c.score,0)+nvl(d.score,0) from student a,y
w b,sx c,yy d  where b.id(+)=a.id and c.id(+)=a.id and d.id(+)=a.id;NAME       NVL(B.SCORE,0)+NVL(C.SCORE,0)+NVL(D.SCORE,0)
---------- --------------------------------------------
zhangsan                                            300
lisi                                                270
wangwu                                              240
zhaoliu                                             140
houqi                                                60
maba                                                100
noname                                                0已选择7行。

Answer 2

这种也可以
SQL> select a.name ,b.ss from student a,(select id,sum(score) ss from ((select *
 from yw) union all (select * from sx) union all (select * from yy)) group by id
) b where a.id=b.id(+);NAME               SS
---------- ----------
zhangsan          300
lisi              270
wangwu            240
zhaoliu           140
houqi              60
maba              100
noname已选择7行。

Answer 3

简单，考虑到某些同学可能没有参加某几门考试，所以肯定要用外连接解决问题,并且如果某个表中缺少相id的记录时对应的score 是null，而null是不能和数字相加的，所以要调用nvl函数处理空值：
select a.id,nvl(b.scores,0)+nvl(c.scores,0)+nvl(d.scores,0)+nvl(e.scores,0)+
            nvl(f.scores,0)+nvl(g.scores,0)  totalscores 
from students       a,
     yuwen_talbe    b,
     shuxue_table   c,
     yingyu_table   d,
     zhengzhi_table e,
     wuli_table     f,
     huaxue_table   g 
where a.id=b.id(+) 
and a.id=c.id(+) 
and a.id=d.id(+) 
and a.id=e.id(+) 
and a.id=f.id(+) 
and a.id=g.id(+);

Answer 4

select id ,sum(Scores) from (
select id,Scores from YuWen_Table
union
select id,Scores from Shuxue_Table
union
select id,Scores from YingYu_Table
union
select id,Scores from ZhengZhi_Table
union
select id,Scores from WuLi_Table
union
select id,Scores from HuaXue_Table
)
group by id

Answer 5

nowait(独行天涯路)  的答案正解！ineedtostudy(amei)  的太长！没心思看！！！不知道对不对！！！