大数据从入门到实战——Hive综合应用案例 ——学生成绩查询_第1关:计算每个班的语文总成绩和数学总成绩

第1关 计算每个班的语文总成绩和数学总成绩

---------- 禁止修改 ----------
 drop database if exists mydb cascade;
  set hive.auto.convert.join = false;
set hive.ignore.mapjoin.hint=false;
---------- 禁止修改 ----------


---------- begin ----------
---创建mydb数据库
create database if not exists mydb; 
---使用mydb数据库
use mydb;
---创建表score
create table if not exists score( name string comment '姓名',
chinese string comment '语文成绩', maths string comment '数学成绩' )
row format delimited fields terminated by ',' stored as textfile;
---导入数据：/root/data/step1_files/score.txt
load data local inpath '/root/data/step1_files/score.txt' into table score;
--创建表class
create table if not exists class( stuname string comment '姓名',
classname string comment '所在班级' )
row format delimited fields terminated by ',' stored as textfile;
---导入数据：/root/data/step1_files/class.txt
load data local inpath '/root/data/step1_files/class.txt' into table class;
---计算每个班的语文总成绩和数学总成绩，要求有哪科低于60分，该名学生成绩不计入计算。
select t1.classname,t1.chinese,t2.maths from(
select c.classname classname,sum(s.chinese) chinese from class c,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 class c
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28