@R星校长
第5
关:统计总成绩
本关任务:计算每个班的语文总成绩和数学总成绩,要求科目中低于60
分的成绩不记录总成绩。
tb_score结构数据:
name | chinese | maths |
---|---|---|
A | 89 | 98 |
B | 99 | 89 |
C | 55 | 66 |
D | 88 | 66 |
E | 55 | 66 |
F | 88 | 99 |
tb_class
表结构数据:
stuname | classname |
---|---|
A | C1 |
B | C2 |
D | C2 |
E | C1 |
F | C3 |
预期输出:
+-----------+---------+-------+
| classname | chinese | maths |
+-----------+---------+-------+
| C1 | 89 | 164 |
| C2 | 187 | 155 |
| C3 | 88 | 165 |
+-----------+---------+-------+
开始你的任务吧,祝你成功!
答案:
#请在此添加实现代码
########## Begin ##########
select t1.classname,t1.chinese,t2.maths
from(select c.classname classname,sum(s.chinese) chinese from tb_class c,tb_score s
where c.stuname=s.name and s.chinese>=60 group by c.classname) t1,
(select c.classname classname,sum(s.maths) maths from tb_class c,tb_score s
where c.stuname=s.name and s.maths>=60 group by c.classname) t2
where t1.classname=t2.classname;
########## End ##########