hive练习含数据准备（0-1）

数据准备

学生表：
01 赵雷 1990-01-01 男
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男
05 周梅 1991-12-01 ⼥
06 吴兰 1992-03-01 ⼥
07 郑⽵ 1989-07-01 ⼥
08 王菊 1990-01-20 ⼥
--------------------------------------------------------
老师表：
01  语⽂  02
02  数学  01
03  英语  03
--------------------------------------------------------
课程表：
01  张三
02  李四
03  王五
--------------------------------------------------------
成绩表：
01  01  80
01  02  90
01  03  99
02  01  70
02  02  60
02  03  80
03  01  80
03  02  80
03  03  80
04  01  50
04  02  30
04  03  20
05  01  76
05  02  87
06  01  31
06  03  34
07  02  89
07  03  98
--------------------------------------------------------

将数据加载到hadoop上面去

hive创建表

create table student(
s_id int,
s_name string,
s_birth string,
s_sex string)
row format delimited fields terminated by ' ';

数据装载

load data inpath '/hive_test_ti/Student' into table  student;

查询数据

依次将剩余三种表建立好,对应关系为：

01、查询"01"课程⽐"02"课程成绩⾼的学⽣的信息及课程分数:

with
sc1 as (select * from score where c_id = 1),
sc2 as (select * from score where c_id = 2)
select s.*,sc1.s_score as score_01,sc2.s_score as score_02
from student s
inner join sc1 on s.s_id = sc1.s_id
inner join sc2 on s.s_id = sc2.s_id
where sc1.s_score > sc2.s_score;

总结：

根据一张表的一个字段的不同类型（01，02课程），比较这张表中另外一个字段（课程成绩）。我们可以将这两个不同的类型拿出来作为两张表，然后通过join，在将这两个字段进行一个横向的拼接，就可以进行课程的比较了；

with
sc1 as (select * from score where c_id = 1),
sc2 as (select * from score where c_id = 2)
select sc1.s_id as id_01,sc1.s_score as score_01,sc2.s_id as id_02,sc2.s_score as score_02
from sc1
inner join sc2 on sc1.s_id=sc2.s_id;

添加where进行比较：

with
sc1 as (select * from score where c_id = 1),
sc2 as (select * from score where c_id = 2)
select sc1.s_id as id_01,sc1.s_score as score_01,sc2.s_id as id_02,sc2.s_score as score_02
from sc1
inner join sc2 on sc1.s_id=sc2.s_id
where sc1.s_score > sc2.s_score;

因为题中还需要查询student相关信息，因此我们需要和student进行join连接：

with
sc1 as (select * from score where c_id = 1),
sc2 as (select * from score where c_id = 2)
select student.*,sc1.s_id as id_01,sc1.s_score as score_01,sc2.s_id as id_02,sc2.s_score as score_02
from sc1
inner join sc2 on sc1.s_id=sc2.s_id
inner join student on student.s_id=sc2.s_id
where sc1.s_score > sc2.s_score;

至此该题完成。

制作不易，关注一下本靓仔吧！