文章目录

  • Hive版本: 3.1.2
  • 数据源
  • 建表语句
  • 导入数据
  • 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
  • 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
  • 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
  • 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
  • 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
  • 6、查询"李"姓老师的数量
  • 7、查询学过"张三"老师授课的同学的信息
  • 8、查询没学过"张三"老师授课的同学的信息
  • 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
  • 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
  • 11、查询没有学全所有课程的同学的信息
  • 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
  • 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
  • 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
  • 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
  • 16、检索"01"课程分数小于60，按分数降序排列的学生信息
  • 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
  • 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
  • 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
  • 19、按各科成绩进行排序，并显示排名
  • 20、查询学生的总成绩并进行排名
  • 21、查询不同老师所教不同课程平均分从高到低显示
  • 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
  • 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
  • 24、查询学生平均成绩及其名次
  • 25、查询各科成绩前三名的记录



Hive版本: 3.1.2

题目是这个博客提供的,答题是自己根据题目写的,所以有些答案和原博主的不一样,sql者是已经验证可以执行并能够获取正确结果的,如果有题目理解错误请大家指出

练习时建议开启本地模式, 大幅缩短执行时间

--开启本地mr
set hive.exec.mode.local.auto=true;

数据源

vim /export/data/hivedatas/student.txt

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 女

vim /export/data/hivedatas/course.txt

01 语文 02
02 数学 01
03 英语 03

vim /export/data/hivedatas/teacher.txt

01 张三
02 李四
03 王五

vim /export/data/hivedatas/score.txt

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

建表语句

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

create table course
(
    c_id   string,
    c_name string,
    t_id   string
) row format delimited fields terminated by ' ';

create table teacher
(
    t_id   string,
    t_name string
) row format delimited fields terminated by ' ';

create table score
(
    s_id    string,
    c_id    string,
    s_score int
) row format delimited fields terminated by ' ';

导入数据

load data local inpath '/export/data/hivedatas/student.txt' into table student;

load data local inpath '/export/data/hivedatas/course.txt' into table course;

load data local inpath '/export/data/hivedatas/teacher.txt' into table teacher;

load data local inpath '/export/data/hivedatas/score.txt' into table score;

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select s.*, s2.s_score score1, s3.s_score score2
from student s
         join score s2 on s.s_id = s2.s_id and s2.c_id = '01'
         join score s3 on s.s_id = s3.s_id and s3.c_id = '02'
where s2.s_score > s3.s_score;

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select s.*, s2.s_score score1, s3.s_score score2
from student s
         join score s2 on s.s_id = s2.s_id and s2.c_id = '01'
         join score s3 on s.s_id = s3.s_id and s3.c_id = '02'
where s2.s_score < s3.s_score;

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select s.s_id, s.s_name, avg(s_score) as score_avg
from student s
         left join score s2 on s.s_id = s2.s_id
group by s.s_id, s.s_name
having score_avg >= 60;
select s.s_id, s.s_name, temp.avg_score
from student s
         right join (select s_id, avg(s_score) as avg_score from score group by s_id having avg_score >= 60) as temp
                    on s.s_id = temp.s_id;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

select s.s_id, s.s_name, temp.avg_score
from student s
         left join (select s_id, avg(s_score) as avg_score
                    from score
                    group by s_id) as temp on s.s_id = temp.s_id
where avg_score < 60
   or avg_score is null;

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select s.s_id, s.s_name, count(s2.c_id) count_course, sum(s2.s_score) sum_score
from student s
         left join score s2 on s.s_id = s2.s_id
group by s.s_id, s.s_name;

6、查询"李"姓老师的数量

select count(*)
from teacher
where t_name like '李%';

7、查询学过"张三"老师授课的同学的信息

select s.*, t.t_name, c.c_name
from student s
         left join score s2 on s.s_id = s2.s_id
         left join course c on s2.c_id = c.c_id
         left join teacher t on c.t_id = t.t_id
where t_name = '张三';

8、查询没学过"张三"老师授课的同学的信息

select s.*
from student s
         left join (select s.*, t.t_name, c.c_name
                    from student s
                             left join score s2 on s.s_id = s2.s_id
                             left join course c on s2.c_id = c.c_id
                             left join teacher t on c.t_id = t.t_id
                    where t_name = '张三') temp
                   on s.s_id = temp.s_id
where temp.t_name is null;
select *
from student
where s_id not in (select s.s_id
                   from student s
                            left join score s2 on s.s_id = s2.s_id
                            left join course c on s2.c_id = c.c_id
                            left join teacher t on c.t_id = t.t_id
                   where t_name = '张三');

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select s.*
from student s
         join score s2 on s.s_id = s2.s_id and s2.c_id = '01'
         join score s3 on s.s_id = s3.s_id and s3.c_id = '02';

```sql
select *
from student
         join (select s_id from score where c_id = 1) tmp1
              on student.s_id = tmp1.s_id
         join (select s_id from score where c_id = 2) tmp2
              on student.s_id = tmp2.s_id;

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select s.*, s3.c_id
from student s
         join score s2 on s.s_id = s2.s_id and s2.c_id = '01'
         left join score s3 on s.s_id = s3.s_id and s3.c_id = '02'
where s3.c_id is null;

11、查询没有学全所有课程的同学的信息

select s.s_id, s.s_name, count(s2.c_id) count
from student s
         left join score s2 on s.s_id = s2.s_id
group by s.s_id, s.s_name
having count < 3;

```sql
select *
from student s
where s_id not in (select s.s_id
                   from score s
                   group by s.s_id
                   having count(c_id) = 3);

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

with t as (select s.*, row_number() over (partition by s.s_id order by s.s_id) rk
           from student s
                    join (select c_id
                          from score
                          where s_id = '01') temp1
                    join (select s_id,
                                 c_id
                          from score) temp2
                         on temp1.c_id = temp2.c_id and s.s_id = temp2.s_id)

select *
from t
where rk = 1;
select s.*, row_number() over (partition by s.s_id order by s.s_id) rk
from student s
         join (select c_id
               from score
               where s_id = '01') temp1
         join (select s_id,
                      c_id
               from score) temp2
              on temp1.c_id = temp2.c_id and s.s_id = temp2.s_id
group by s.s_id, s.s_name, s.s_birth, s.s_sex;

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

select s.*
from student s
         join (select s_id, concat_ws('|', collect_set(c_id)) course1
               from score
               group by s_id
               having s_id != '01') temp1
              on s.s_id = temp1.s_id
         join (select concat_ws('|', collect_set(c_id)) course2 from score group by s_id having s_id = '01') temp2
              on temp1.course1 = temp2.course2;

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select s.*
from student s
         left join (select *
                    from score s2
                             join course c on s2.c_id = c.c_id
                             join teacher t on c.t_id = t.t_id and t_name = '张三') t1 on s.s_id = t1.s_id
where t1.t_name is null;

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select s.*, s2.avg_score
from student s
         join (select s_id,
                      count(c_id)  count_fail,
                      avg(s_score) avg_score
               from score
               where s_score < 60
               group by s_id) s2
              on s.s_id = s2.s_id;

16、检索"01"课程分数小于60，按分数降序排列的学生信息

select s.*
from student s
         join (select *
               from score
               where c_id = '01'
                 and s_score < 60) s2 on s.s_id = s2.s_id
order by s2.s_score desc;

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select s1.*, s2.s_score, avg(s_score) over (partition by s1.s_id) avg_score
from student s1,
     score s2
where s1.s_id = s2.s_id
order by avg_score desc;

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select c.c_id,
       c.c_name,
       max(s.s_score)                                                                                max_score,
       min(s.s_score)                                                                                min_score,
       round(avg(s.s_score), 2)                                                                      avg_score,
       round(sum(case when s.s_score >= 60 then 1 else 0 end) / count(c.c_id), 2)                    passRate,
       round(sum(case when s.s_score >= 70 and s.s_score < 80 then 1 else 0 end) / count(c.c_id), 2) middleRate,
       round(sum(case when s.s_score >= 80 and s.s_score < 90 then 1 else 0 end) / count(c.c_id), 2) goodRate,
       round(sum(case when s.s_score >= 90 then 1 else 0 end) / count(c.c_id), 2)                    excellentRate
from course c,
     score s
where c.c_id = s.c_id
group by c.c_id, c.c_name;

19、按各科成绩进行排序，并显示排名

select *, row_number() over (partition by c_id order by s_score desc ) rk
from score;

20、查询学生的总成绩并进行排名

select s1.s_id,
       sum(s2.s_score)                                    sum_score,
       row_number() over (order by sum(s2.s_score) desc ) rk
from student s1,
     score s2
where s1.s_id = s2.s_id
group by s1.s_id;

21、查询不同老师所教不同课程平均分从高到低显示

select t.t_name, s.c_id, avg(s_score) avg_score
from score s
         join course c on s.c_id = c.c_id
         join teacher t on c.t_id = t.t_id
group by s.c_id, t.t_name
order by avg_score desc;

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

select *
from student s
         join (with t as (select *, dense_rank() over (partition by c_id order by s_score desc ) rk
                          from score)
               select *
               from t
               where rk = 2
                  or rk = 3) s2 on s.s_id = s2.s_id;

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select c.c_id,
       c.c_name,
       sum(case when s.s_score >= 85 then 1 else 0 end)                                              s100_85,
       sum(case when s.s_score >= 70 and s.s_score < 85 then 1 else 0 end)                           s85_70,
       sum(case when s.s_score >= 60 and s.s_score < 70 then 1 else 0 end)                           s70_60,
       sum(case when s.s_score < 60 then 1 else 0 end)                                               s60_0,
       round(sum(case when s.s_score >= 85 then 1 else 0 end) / count(s.c_id), 2)                    s100_85_Rate,
       round(sum(case when s.s_score >= 70 and s.s_score < 85 then 1 else 0 end) / count(s.c_id), 2) s85_70_Rate,
       round(sum(case when s.s_score >= 60 and s.s_score < 70 then 1 else 0 end) / count(s.c_id), 2) s70_60_Rate,
       round(sum(case when s.s_score < 60 then 1 else 0 end) / count(s.c_id), 2)                     s60_0_Rate
from course c
         join score s on c.c_id = s.c_id
group by c.c_id, c.c_name;

24、查询学生平均成绩及其名次

select s.s_id,
       s.s_name,
       round(avg(s2.s_score), 2)                         avg_score,
       dense_rank() over (order by avg(s2.s_score) desc) rk
from student s
         join score s2 on s.s_id = s2.s_id
group by s.s_id, s.s_name;

25、查询各科成绩前三名的记录

with t1 as (select s.*,
                   s2.s_score,
                   c.*,
                   dense_rank() over (partition by s2.c_id order by s2.s_score) rk
            from student s
                     join score s2 on s.s_id = s2.s_id
                     left join course c on s2.c_id = c.c_id)

select *
from t1
where rk < 4;