HiveSQL题库-初级
目录
- HiveSQL题库-初级
- 第一章 环境准备
- 第二章 简单查询
- 第三章 汇总分析
- 第四章 复杂查询
- 第五章 多表查询
- 5.1 表连接
- 5.2 多表连接
- 5.2.1 检索"01"课程分数小于60，按分数降序排列的学生信息
- 5.2.2 查询任何一门课程成绩在70分以上的学生的姓名、课程名称和分数，按分数升序排列
- 5.2.2 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩(包括不及格的分数)
- 5.2.3 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
- 5.2.4 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
- 5.2.5 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
- 5.2.6 查询学过“李体音”老师所教的所有课的同学的学号、姓名
- 5.2.7 查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名
- 5.2.8 查询选修“李体音”老师所授课程的学生中成绩最高的学生姓名及其成绩（与上题类似,用成绩排名，用 limit 1得出最高一个）
- 5.2.9 查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名
- 5.2.10 查询所学课程与学号为“001”的学生所学课程完全相同的学生的学号和姓名
- 5.2.11 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
- 第流章 窗口查询
第一章 环境准备
1.1 Hive启动
-- 本地连接
nohup hive --service metastore &
hive
-- 远程连接
-- 服务端
nohup hive --service metastore &
nohup hiveserver2 &
-- 远程端(进入hive的bin目录下启动)
./beeline -u jdbc:hive2://master:10000 -n root
-- nohup是后台启动
1.2 见表语句
-- 创建学生表
DROP TABLE IF EXISTS student;
create table if not exists student(
stu_id string COMMENT '学生id',
stu_name string COMMENT '学生姓名',
birthday date COMMENT '出生日期',
sex string COMMENT '性别'
)
row format delimited fields terminated by ','
stored as textfile;
-- 创建课程表
DROP TABLE IF EXISTS course;
create table if not exists course(
course_id string COMMENT '课程id',
course_name string COMMENT '课程名',
tea_id string COMMENT '任课老师id'
)
row format delimited fields terminated by ','
stored as textfile;
-- 创建老师表
DROP TABLE IF EXISTS teacher;
create table if not exists teacher(
tea_id string COMMENT '老师id',
tea_name string COMMENT '老师姓名'
)
row format delimited fields terminated by ','
stored as textfile;
-- 创建分数表
DROP TABLE IF EXISTS score;
create table if not exists score(
stu_id string COMMENT '学生id',
course_id string COMMENT '课程id',
course int COMMENT '成绩'
)
row format delimited fields terminated by ','
stored as textfile;
1.3 数据准备
- 创建/usr/local/soft/data目录
[root@master soft]# mkdir data
- 将如下4个文件放到/usr/local/soft/data目录下
1.4 插入数据
(1) 插入数据
load data local inpath '/usr/local/soft/data/student.txt' into table student;
load data local inpath '/usr/local/soft/data/course.txt' into table course;
load data local inpath '/usr/local/soft/data/teacher.txt' into table teacher;
load data local inpath '/usr/local/soft/data/score.txt' into table score;
(2) 验证插入数据情况
select * from student limit 5;
select * from course limit 5;
select * from teacher limit 5;
select * from score limit 5;
第二章 简单查询
2.1 查找特定条件
2.1.1 查询姓名中带“冰”的学生名单
select *
from student where stu_name like "%冰%";
- 结果
+-----------------+-------------------+-------------------+--------------+--+
| student.stu_id | student.stu_name | student.birthday | student.sex |
+-----------------+-------------------+-------------------+--------------+--+
| 017 | 范冰冰 | 1992-07-04 | 女 |
| 018 | 李冰冰 | 1993-09-24 | 女 |
+-----------------+-------------------+-------------------+--------------+--+
2.1.2 查询姓王老师的个数
select count(1) as wang_cnt
from teacher
where tea_name like "王%";
- 结果
+-----------+--+
| wang_cnt |
+-----------+--+
| 1 |
+-----------+--+
2.1.3 检索课程编号为“04”且分数小于60的学生的课程信息，结果按分数降序排序
select *
from score
where course_id="04" and course<60
order by course desc;
- 结果
+---------------+------------------+---------------+--+
| score.stu_id | score.course_id | score.course |
+---------------+------------------+---------------+--+
| 004 | 04 | 59 |
| 001 | 04 | 54 |
| 020 | 04 | 50 |
| 014 | 04 | 40 |
| 017 | 04 | 34 |
| 010 | 04 | 34 |
+---------------+------------------+---------------+--+
2.1.4 查询数学成绩不及格的学生和其对应的成绩，按学号升序排序
select s1.stu_id
,s1.stu_name
,s2.course
from student as s1
join (
select stu_id
,course
from score
where course_id in (
select course_id
from course
where course_name="数学"
) and course<60
) s2 on s1.stu_id=s2.stu_id
order by stu_id asc
;
- 结果
+------------+--------------+------------+--+
| s1.stu_id | s1.stu_name | s2.course |
+------------+--------------+------------+--+
| 005 | 唐国强 | 44 |
| 007 | 陈坤 | 55 |
| 008 | 吴京 | 34 |
| 011 | 潘长江 | 49 |
| 013 | 蒋欣 | 35 |
| 014 | 赵丽颖 | 39 |
| 015 | 刘亦菲 | 48 |
| 017 | 范冰冰 | 34 |
| 018 | 李冰冰 | 58 |
| 019 | 邓紫棋 | 39 |
| 020 | 宋丹丹 | 59 |
+------------+--------------+------------+--+
2.2 日期时间相关
2.2.1 查询各学生的年龄(精确到月份)(current_date函数)
select
s1.stu_name
,concat(
if(s1.month>=0,s1.year,s1.year-1),"岁",
if(s1.month>=0,month,12+month),"个月"
) as age
from (
select stu_name
,year(current_date()) - year(birthday) `year`
,month(current_date()) - month(birthday) `month`
from student
) s1
;
- 结果
+--------------+----------+--+
| s1.stu_name | age |
+--------------+----------+--+
| 彭于晏 | 28岁0个月 |
| 胡歌 | 29岁2个月 |
| 周杰伦 | 28岁1个月 |
| 刘德华 | 24岁9个月 |
| 唐国强 | 29岁8个月 |
| 陈道明 | 30岁6个月 |
| 陈坤 | 24岁1个月 |
| 吴京 | 29岁3个月 |
| 郭德纲 | 30岁5个月 |
| 于谦 | 24岁9个月 |
| 潘长江 | 28岁0个月 |
| 杨紫 | 26岁5个月 |
| 蒋欣 | 25岁6个月 |
| 赵丽颖 | 33岁4个月 |
| 刘亦菲 | 30岁4个月 |
| 周冬雨 | 32岁11个月 |
| 范冰冰 | 30岁10个月 |
| 李冰冰 | 29岁8个月 |
| 邓紫棋 | 28岁9个月 |
| 宋丹丹 | 32岁2个月 |
+--------------+----------+--+
2.2.2 查询本月过生日的学生
select
stu_name
,birthday
from student
where month(birthday)=month(current_date())
;
- 结果
+-----------+-------------+--+
| stu_name | birthday |
+-----------+-------------+--+
| 彭于晏 | 1995-05-16 |
| 潘长江 | 1995-05-27 |
+-----------+-------------+--+
;
第三章 汇总分析
3.1 汇总分析
3.1.1 查询课程编号为“02”的总成绩
select
course_id
,sum(course) as course_sum
from score
where course_id="02"
group by course_id
;
- 结果
+------------+-------------+--+
| course_id | course_sum |
+------------+-------------+--+
| 02 | 1133 |
+------------+-------------+--+
3.1.2 查询参加考试的学生个数
select
count(distinct stu_id) as cnt
from score
;
- 结果
+------+--+
| cnt |
+------+--+
| 19 |
+------+--+
3.2 分组
3.2.1 查询各科成绩最高和最低的分，以如下形式显示：课程号，最高分，最低分
select
course_id
,max(course) as max_course
,min(course) as min_course
from score
group by course_id
;
- 结果
+------------+-------------+-------------+--+
| course_id | max_course | min_course |
+------------+-------------+-------------+--+
| 01 | 94 | 38 |
| 02 | 93 | 34 |
| 03 | 99 | 32 |
| 04 | 100 | 34 |
| 05 | 87 | 59 |
+------------+-------------+-------------+--+
3.2.2 查询每门课程有多少学生参加了考试(有成绩的学生)
-- 按课程分组，统计有成绩学生的人数
select
course_id
,count(stu_id) as stu_cnt
from score
group by course_id
;
- 结果
+------------+----------+--+
| course_id | stu_cnt |
+------------+----------+--+
| 01 | 19 |
| 02 | 19 |
| 03 | 19 |
| 04 | 12 |
| 05 | 5 |
+------------+----------+--+
3.2.3 查询男生、女生人数
-- 按性别分组，查询student表，并统计人数
select
sex
,count(stu_id) as stu_cnt
from student
group by sex
;
- 结果
+------+----------+--+
| sex | stu_cnt |
+------+----------+--+
| 女 | 9 |
| 男 | 11 |
+------+----------+--+
3.3 分组结果的条件
3.3.1 查询平均成绩大于60分学生的学号和平均成绩
-- 根据学生id分组计算平均成绩，并判断大于60
select
stu_id
,avg(course) as course_avg
from score
group by stu_id
having course_avg > 60
;
- 结果
+---------+--------------------+--+
| stu_id | course_avg |
+---------+--------------------+--+
| 001 | 72.5 |
| 002 | 86.25 |
| 004 | 81.5 |
| 005 | 75.4 |
| 006 | 73.33333333333333 |
| 009 | 74.2 |
| 013 | 61.0 |
| 015 | 70.25 |
| 016 | 81.25 |
| 020 | 69.75 |
+---------+--------------------+--+
3.3.2 查询至少选修两门课的学生的学号
-- 按学号分组，统计有成绩的科目数量，查询大于等于2门的学生学号
select
stu_id
from score
group by stu_id
having count(course_id) >= 2
;
- 结果
+---------+--+
| stu_id |
+---------+--+
| 001 |
| 002 |
| 004 |
| 005 |
| 006 |
| 007 |
| 008 |
| 009 |
| 010 |
| 011 |
| 012 |
| 013 |
| 014 |
| 015 |
| 016 |
| 017 |
| 018 |
| 019 |
| 020 |
+---------+--+
3.3.3 查询同姓(假设每个学生姓名的第一个字为姓)的学生的名单并统计各人数至少为2个的姓
-- 按同姓分组统计人数
select
s1.stu_x
,count(1) as cnt
from (
-- 得到所有学生的姓
select
substring(stu_name,1,1) as stu_x
from student
) s1
group by s1.stu_x
having cnt>=2
;
- 结果
+-----------+------+--+
| s1.stu_x | cnt |
+-----------+------+--+
| 刘 | 2 |
| 周 | 2 |
| 陈 | 2 |
+-----------+------+--+
3.3.4 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排序
select
course_id
,avg(course) as course_avg
from score
group by course_id
order by course_avg asc,course_id desc
;
- 结果
+------------+---------------------+--+
| course_id | course_avg |
+------------+---------------------+--+
| 02 | 59.63157894736842 |
| 04 | 63.416666666666664 |
| 01 | 67.15789473684211 |
| 03 | 69.42105263157895 |
| 05 | 74.6 |
+------------+---------------------+--
3.3.5 统计参加考试人数大于等于15的学科
select
course_id
,count(1) as cnt
from score
group by course_id
having cnt >= 15
;
- 结果
+------------+------+--+
| course_id | cnt |
+------------+------+--+
| 01 | 19 |
| 02 | 19 |
| 03 | 19 |
+------------+------+--+
3.4 查询结果排序&分组指定条件
3.4.1 查询学生的总成绩并按照总成绩进行降序排序
select
stu_id
,sum(course) as sum_course
from score
group by stu_id
order by sum_course desc
;
- 结果
+---------+-------------+--+
| stu_id | sum_course |
+---------+-------------+--+
| 005 | 377 |
| 009 | 371 |
| 002 | 345 |
| 004 | 326 |
| 016 | 325 |
| 007 | 299 |
| 001 | 290 |
| 015 | 281 |
| 020 | 279 |
| 013 | 244 |
| 010 | 233 |
| 018 | 232 |
| 006 | 220 |
| 014 | 192 |
| 017 | 181 |
| 012 | 180 |
| 011 | 180 |
| 019 | 178 |
| 008 | 129 |
+---------+-------------+--+
3.4.2 查询平均成绩大于60分的学生的学号和平均成绩
select
stu_id
,avg(course) as avg_course
from score
group by stu_id
having avg_course > 60
;
- 结果
+---------+--------------------+--+
| stu_id | avg_course |
+---------+--------------------+--+
| 001 | 72.5 |
| 002 | 86.25 |
| 004 | 81.5 |
| 005 | 75.4 |
| 006 | 73.33333333333333 |
| 009 | 74.2 |
| 013 | 61.0 |
| 015 | 70.25 |
| 016 | 81.25 |
| 020 | 69.75 |
+---------+--------------------+--+
3.4.3 按照如下格式显示学生的语文、数学、英语三科成绩，没有成绩的输出为0，按照学生的有效平均成绩降序显示
学生id 学生姓名 语文 数学 英语 有效课程数 有效平均成绩
select
stu_id as `学生ID`,
(select nvl(course,0) as course from score where score.stu_id = t.stu_id and course_id='01') as `语文`,
(select nvl(course,0) as course from score where score.stu_id = t.stu_id and course_id='02') as `数学`,
(select nvl(course,0) as course from score where score.stu_id = t.stu_id and course_id='03') as `英语`,
count(*) as `有效课程数`,
avg(t.course) as `平均成绩`
from score t
group by stu_id
order by avg(t.course) desc;
- 结果
学生id 语文 数学 英语 有效课程数 平均成绩
002 74 84 87 4 86.25
004 85 93 89 4 81.5
016 71 89 71 4 81.25
005 64 44 99 5 75.4
009 75 78 60 5 74.2
006 71 90 59 3 73.33333333333333
001 94 63 79 4 72.5
015 90 48 84 4 70.25
020 89 59 81 4 69.75
013 47 35 93 4 61.0
012 44 74 62 3 60.0
011 61 49 70 3 60.0
007 48 55 70 5 59.8
019 46 39 93 3 59.333333333333336
010 84 68 47 4 58.25
018 38 58 49 4 58.0
014 81 39 32 4 48.0
017 58 34 55 4 45.25
008 56 34 39 3 43.0
3.4.4 查询一共参加三门课程且其中一门为语文课程的学生的id和姓名
思路：
1 查询一共参加三门课程的学生id
2 在1基础上，查询其中一门是语文的学生id
3 与student表关联，查询字段学生id和姓名
-- 3 与student表关联，查询字段学生id和姓名
select
s2.stu_id
,s1.stu_name
from student s1
join (
-- 2 在1基础上，查询其中一门是语文的学生id
select
stu_id
from score
where stu_id in (
-- 1 查询一共参加三门课程的学生id
select
stu_id
from score
group by stu_id
having count(course_id) = 3
) and course_id="01"
) s2 on s1.stu_id=s2.stu_id
;
- 结果
+------------+--------------+--+
| s2.stu_id | s1.stu_name |
+------------+--------------+--+
| 006 | 陈道明 |
| 008 | 吴京 |
| 011 | 潘长江 |
| 012 | 杨紫 |
| 019 | 邓紫棋 |
+------------+--------------+--+
第四章 复杂查询
4.1 子查询
4.1.1 查询所有课程成绩都小于60分学生的学号、姓名
思路：
如果分数大于等于60赋值为1，否则赋值为0，故按学生id分组，求赋值后的和等于0的则表明每门课都是小于60的。
select
s2.stu_id
,s1.stu_name
from student s1
join (
select
stu_id
,sum(if(course>=60,1,0)) flage
from score
group by stu_id
having flage=0
) s2 on s1.stu_id=s2.stu_id
;
- 结果
+------------+--------------+--+
| s2.stu_id | s1.stu_name |
+------------+--------------+--+
| 008 | 吴京 |
| 017 | 范冰冰 |
+------------+--------------+--+
4.1.2 查询没有学全所有课的学生的学号、姓名
按学生id分组统计学生考试的所有科目数量
查询小于课程总数的学生id
与student表关联查询姓名
select
ss1.stu_id
,ss1.stu_name
from(
select
s2.stu_id
,s1.stu_name
,s2.course_cnt
,1 ff
from student s1
join (
select
stu_id
,count(distinct course_id) course_cnt
from score
group by stu_id
) s2
on s1.stu_id=s2.stu_id
) ss1
left join(
select
count(distinct course_id) course
,1 ff
from course
) s3
on ss1.ff=s3.ff
where ss1.course_cnt<s3.course
;
- 结果
+-------------+---------------+--+
| ss1.stu_id | ss1.stu_name |
+-------------+---------------+--+
| 001 | 彭于晏 |
| 002 | 胡歌 |
| 004 | 刘德华 |
| 006 | 陈道明 |
| 008 | 吴京 |
| 010 | 于谦 |
| 011 | 潘长江 |
| 012 | 杨紫 |
| 013 | 蒋欣 |
| 014 | 赵丽颖 |
| 015 | 刘亦菲 |
| 016 | 周冬雨 |
| 017 | 范冰冰 |
| 018 | 李冰冰 |
| 019 | 邓紫棋 |
| 020 | 宋丹丹 |
+-------------+---------------+--+
4.1.3 查询出只选修了三门课程的全部学生的学号和姓名
select
stu_id
,stu_name
from student
where stu_id in (
select
stu_id
from score
group by stu_id
having count(course_id) = 3
)
;
- 结果
+---------+-----------+--+
| stu_id | stu_name |
+---------+-----------+--+
| 006 | 陈道明 |
| 008 | 吴京 |
| 011 | 潘长江 |
| 012 | 杨紫 |
| 019 | 邓紫棋 |
+---------+-----------+--+
4.1.4 查找1990年出生的学生名单
select
stu_id
,stu_name
from student
where date_format(birthday,'YYYY') = 1990
;
- 结果
+---------+-----------+--+
| stu_id | stu_name |
+---------+-----------+--+
| 014 | 赵丽颖 |
| 016 | 周冬雨 |
+---------+-----------+--+
第五章 多表查询
5.1 表连接
5.1.1 查询两门以上不及格课程的同学的学号及其平均成绩
-- 方法一
select
c1.stu_id
,avg(c1.course) as avg_course
from(
select
stu_id
,course
,if(course<60,1,0) cs
from score
) c1
group by c1.stu_id
having sum(cs) >= 2
;
-- 方法二
select
t1.stu_id,
t2.course_avg
from (
select
stu_id,
sum(if(course<60,1,0)) flage
from score
group by stu_id
having flage >=2
) t1
join (
select
stu_id,
avg(course) course_avg
from score
group by stu_id
) t2
on t1.stu_id = t2.stu_id
;
- 结果
+------------+---------------------+--+
| t1.stu_id | t2.course_avg |
+------------+---------------------+--+
| 007 | 59.8 |
| 008 | 43.0 |
| 010 | 58.25 |
| 013 | 61.0 |
| 014 | 48.0 |
| 015 | 70.25 |
| 017 | 45.25 |
| 018 | 58.0 |
| 019 | 59.333333333333336 |
| 020 | 69.75 |
+------------+---------------------+--+
5.1.2 查询所有学生的学号、姓名、选课数、总成绩
select
s1.stu_id
,s1.stu_name
,s2.course_cnt
,s2.course_sum
from student s1
left join(
select
stu_id
,count(course_id) course_cnt
,sum(course) course_sum
from score
group by stu_id
) s2
on s1.stu_id = s2.stu_id
;
- 结果
+------------+--------------+----------------+----------------+--+
| s1.stu_id | s1.stu_name | s2.course_cnt | s2.course_sum |
+------------+--------------+----------------+----------------+--+
| 001 | 彭于晏 | 4 | 290 |
| 002 | 胡歌 | 4 | 345 |
| 003 | 周杰伦 | NULL | NULL |
| 004 | 刘德华 | 4 | 326 |
| 005 | 唐国强 | 5 | 377 |
| 006 | 陈道明 | 3 | 220 |
| 007 | 陈坤 | 5 | 299 |
| 008 | 吴京 | 3 | 129 |
| 009 | 郭德纲 | 5 | 371 |
| 010 | 于谦 | 4 | 233 |
| 011 | 潘长江 | 3 | 180 |
| 012 | 杨紫 | 3 | 180 |
| 013 | 蒋欣 | 4 | 244 |
| 014 | 赵丽颖 | 4 | 192 |
| 015 | 刘亦菲 | 4 | 281 |
| 016 | 周冬雨 | 4 | 325 |
| 017 | 范冰冰 | 4 | 181 |
| 018 | 李冰冰 | 4 | 232 |
| 019 | 邓紫棋 | 3 | 178 |
| 020 | 宋丹丹 | 4 | 279 |
+------------+--------------+----------------+----------------+--+
5.1.3 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select
s2.stu_id
,s1.stu_name
,s2.avg_course
from student s1
join(
select
stu_id
,avg(course) avg_course
from score
group by stu_id
having avg_course > 85
) s2
on s1.stu_id = s2.stu_id
;
- 结果
+------------+--------------+----------------+--+
| s2.stu_id | s1.stu_name | s2.avg_course |
+------------+--------------+----------------+--+
| 002 | 胡歌 | 86.25 |
+------------+--------------+----------------+--+
5.1.4 查询学生的选课情况：学号，姓名，课程号，课程名称
select
s1.stu_id
,s2.stu_name
,c1.course_id
,c1.course_name
from score as s1
join student s2
on s1.stu_id=s2.stu_id
join course c1
on s1.course_id=c1.course_id
;
- 结果(部分结果总结过74行)
+-----------+-------------+--------------+----------------+--+
| s.stu_id | s.stu_name | c.course_id | c.course_name |
+-----------+-------------+--------------+----------------+--+
| 001 | 彭于晏 | 01 | 语文 |
| 002 | 胡歌 | 01 | 语文 |
| 004 | 刘德华 | 01 | 语文 |
| 005 | 唐国强 | 01 | 语文 |
| 006 | 陈道明 | 01 | 语文 |
| 007 | 陈坤 | 01 | 语文 |
| 008 | 吴京 | 01 | 语文 |
| 009 | 郭德纲 | 01 | 语文 |
| 010 | 于谦 | 01 | 语文 |
| 011 | 潘长江 | 01 | 语文 |
| 012 | 杨紫 | 01 | 语文 |
| 013 | 蒋欣 | 01 | 语文 |
| 014 | 赵丽颖 | 01 | 语文 |
| 015 | 刘亦菲 | 01 | 语文 |
| 016 | 周冬雨 | 01 | 语文 |
+-----------+-------------+--------------+----------------+--+
5.1.5 查询出每门课程的及格人数和不及格人数
select
c.course_id,
c.course_name,
t1.`及格人数`,
t1.`不及格人数`
from course c
join (
select
course_id,
sum(if(course>=60,1,0)) as `及格人数`,
sum(if(course<60,1,0)) as `不及格人数`
from score
group by course_id
) t1
on c.course_id = t1.course_id
;
- 结果
+--------------+----------------+----------+-----------+--+
| c.course_id | c.course_name | t1.及格人数 | t1.不及格人数 |
+--------------+----------------+----------+-----------+--+
| 01 | 语文 | 12 | 7 |
| 02 | 数学 | 8 | 11 |
| 03 | 英语 | 13 | 6 |
| 04 | 体育 | 6 | 6 |
| 05 | 音乐 | 4 | 1 |
+--------------+----------------+----------+-----------+--+
5.1.6 使用分段[100-85),[85-70),[70-60),[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
select
cc1.ff
,cc1.course_id
,cc2.course_name
,cc1.stu_cnt
from course cc2
join(
select
1 ff
,c1.course_id
,count(stu_id) stu_cnt
from(
select
stu_id
,course_id
,course
from score
where course > 85 and course <= 100
) c1
group by c1.course_id
) cc1
on cc1.course_id=cc2.course_id
union all
select
cc1.ff
,cc1.course_id
,cc2.course_name
,cc1.stu_cnt
from course cc2
join(
select
2 ff
,c1.course_id
,count(stu_id) stu_cnt
from(
select
stu_id
,course_id
,course
from score
where course > 70 and course <= 85
) c1
group by c1.course_id
) cc1
on cc1.course_id=cc2.course_id
union all
select
cc1.ff
,cc1.course_id
,cc2.course_name
,cc1.stu_cnt
from course cc2
join(
select
3 ff
,c1.course_id
,count(stu_id) stu_cnt
from(
select
stu_id
,course_id
,course
from score
where course > 60 and course <= 70
) c1
group by c1.course_id
) cc1
on cc1.course_id=cc2.course_id
union all
select
cc1.ff
,cc1.course_id
,cc2.course_name
,cc1.stu_cnt
from course cc2
join(
select
4 ff
,c1.course_id
,count(stu_id) stu_cnt
from(
select
stu_id
,course_id
,course
from score
where course <= 60
) c1
group by c1.course_id
) cc1
on cc1.course_id=cc2.course_id
;
- 结果
+---------+----------------+------------------+--------------+--+
| _u1.ff | _u1.course_id | _u1.course_name | _u1.stu_cnt |
+---------+----------------+------------------+--------------+--+
| 1 | 01 | 语文 | 3 |
| 1 | 02 | 数学 | 3 |
| 1 | 03 | 英语 | 5 |
| 1 | 04 | 体育 | 2 |
| 1 | 05 | 音乐 | 1 |
| 2 | 01 | 语文 | 7 |
| 2 | 02 | 数学 | 3 |
| 2 | 03 | 英语 | 4 |
| 2 | 04 | 体育 | 2 |
| 2 | 05 | 音乐 | 2 |
| 3 | 01 | 语文 | 2 |
| 3 | 02 | 数学 | 2 |
| 3 | 03 | 英语 | 3 |
| 3 | 04 | 体育 | 2 |
| 3 | 05 | 音乐 | 1 |
| 4 | 01 | 语文 | 7 |
| 4 | 02 | 数学 | 11 |
| 4 | 03 | 英语 | 7 |
| 4 | 04 | 体育 | 6 |
| 4 | 05 | 音乐 | 1 |
+---------+----------------+------------------+--------------+--+
5.1.7 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名及课程信息
select
s1.stu_id
,s2.stu_name
,s1.course
,s1.course_id
,c1.course_name
from(
select
stu_id
,course_id
,course
from score
where course_id="03" and course > 80
) s1
join student s2
on s1.stu_id=s2.stu_id
join course c1
on c1.course_id=s1.course_id
;
- 结果
+------------+--------------+------------+---------------+--------------
---+--+| s1.stu_id | s2.stu_name | s1.course | s1.course_id | c1.course_nam
e |+------------+--------------+------------+---------------+--------------
---+--+| 002 | 胡歌 | 87 | 03 | 英语
|| 004 | 刘德华 | 89 | 03 | 英语
|| 005 | 唐国强 | 99 | 03 | 英语
|| 013 | 蒋欣 | 93 | 03 | 英语
|| 015 | 刘亦菲 | 84 | 03 | 英语
|| 019 | 邓紫棋 | 93 | 03 | 英语
|| 020 | 宋丹丹 | 81 | 03 | 英语
|+------------+--------------+------------+---------------+--------------
---+--+
5.1.8 （重要！行转列）使用sql实现将该表行转列为下面的表结构
学号 课程01 课程02 课程03 课程04
001 94 63 79 54
-- 方法一
select
stu_id,
max(if(course_id='01',course,0)) as `语文`,
max(if(course_id='02',course,0)) as `数学`,
max(if(course_id='03',course,0)) as `英语`,
max(if(course_id='04',course,0)) as `体育`
from score
group by stu_id;
-- 方法二
select stu_id,
max(case course_id when '01' then course else 0 end) as `语文`,
max(case course_id when '02' then course else 0 end) as `数学`,
max(case course_id when '03' then course else 0 end) as `英语`,
max(case course_id when '04' then course else 0 end) as `体育`
from score
group by stu_id;
- 结果
+---------+-----+-----+-----+------+--+
| stu_id | 语文 | 数学 | 英语 | 体育 |
+---------+-----+-----+-----+------+--+
| 001 | 94 | 63 | 79 | 54 |
| 002 | 74 | 84 | 87 | 100 |
| 004 | 85 | 93 | 89 | 59 |
| 005 | 64 | 44 | 99 | 85 |
| 006 | 71 | 90 | 59 | 0 |
| 007 | 48 | 55 | 70 | 63 |
| 008 | 56 | 34 | 39 | 0 |
| 009 | 75 | 78 | 60 | 79 |
| 010 | 84 | 68 | 47 | 34 |
| 011 | 61 | 49 | 70 | 0 |
| 012 | 44 | 74 | 62 | 0 |
| 013 | 47 | 35 | 93 | 69 |
| 014 | 81 | 39 | 32 | 40 |
| 015 | 90 | 48 | 84 | 0 |
| 016 | 71 | 89 | 71 | 94 |
| 017 | 58 | 34 | 55 | 34 |
| 018 | 38 | 58 | 49 | 0 |
| 019 | 46 | 39 | 93 | 0 |
| 020 | 89 | 59 | 81 | 50 |
+---------+-----+-----+-----+------+--+
5.2 多表连接
5.2.1 检索"01"课程分数小于60，按分数降序排列的学生信息
select
s1.*
,s2.course
from student s1
right join(
select
stu_id
,course
from score
where course_id="01" and course < 60
order by course desc
) s2
on s1.stu_id=s2.stu_id
;
- 结果
+------------+--------------+--------------+---------+------------+--+
| s1.stu_id | s1.stu_name | s1.birthday | s1.sex | s2.course |
+------------+--------------+--------------+---------+------------+--+
| 017 | 范冰冰 | 1992-07-04 | 女 | 58 |
| 008 | 吴京 | 1994-02-06 | 男 | 56 |
| 007 | 陈坤 | 1999-04-09 | 男 | 48 |
| 013 | 蒋欣 | 1997-11-08 | 女 | 47 |
| 019 | 邓紫棋 | 1994-08-31 | 女 | 46 |
| 012 | 杨紫 | 1996-12-21 | 女 | 44 |
| 018 | 李冰冰 | 1993-09-24 | 女 | 38 |
+------------+--------------+--------------+---------+------------+--+
5.2.2 查询任何一门课程成绩在70分以上的学生的姓名、课程名称和分数，按分数升序排列
select
ss1.stu_name
,ss1.course_name
,ss1.course
from(
select
s2.stu_name
,c1.course_name
,s1.course
from score s1
join student s2
on s1.stu_id = s2.stu_id
join course c1
on c1.course_id = s1.course_id
) ss1
where ss1.course > 70
order by ss1.course asc
;
- 结果
+---------------+------------------+-------------+--+
| ss1.stu_name | ss1.course_name | ss1.course |
+---------------+------------------+-------------+--+
| 周冬雨 | 语文 | 71 |
| 陈道明 | 语文 | 71 |
| 周冬雨 | 英语 | 71 |
| 杨紫 | 数学 | 74 |
| 胡歌 | 语文 | 74 |
| 郭德纲 | 语文 | 75 |
| 郭德纲 | 数学 | 78 |
| 郭德纲 | 体育 | 79 |
| 彭于晏 | 英语 | 79 |
| 郭德纲 | 音乐 | 79 |
| 赵丽颖 | 语文 | 81 |
| 宋丹丹 | 英语 | 81 |
| 于谦 | 语文 | 84 |
| 胡歌 | 数学 | 84 |
| 刘亦菲 | 英语 | 84 |
| 刘德华 | 语文 | 85 |
| 唐国强 | 体育 | 85 |
| 唐国强 | 音乐 | 85 |
| 胡歌 | 英语 | 87 |
| 李冰冰 | 音乐 | 87 |
| 周冬雨 | 数学 | 89 |
| 刘德华 | 英语 | 89 |
| 宋丹丹 | 语文 | 89 |
| 刘亦菲 | 语文 | 90 |
| 陈道明 | 数学 | 90 |
| 刘德华 | 数学 | 93 |
| 蒋欣 | 英语 | 93 |
| 邓紫棋 | 英语 | 93 |
| 周冬雨 | 体育 | 94 |
| 彭于晏 | 语文 | 94 |
| 唐国强 | 英语 | 99 |
| 胡歌 | 体育 | 100 |
+---------------+------------------+-------------+--+
5.2.2 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩(包括不及格的分数)
select
s2.stu_id
,s1.stu_name
,s2.avg_course
from student s1
join(
select
stu_id
,avg(course) avg_course
from score
group by stu_id
having sum(if(course >= 60, 0, 1)) >= 2
) s2
on s1.stu_id = s2.stu_id
;
- 结果
+------------+--------------+---------------------+--+
| s2.stu_id | s1.stu_name | s2.avg_course |
+------------+--------------+---------------------+--+
| 007 | 陈坤 | 59.8 |
| 008 | 吴京 | 43.0 |
| 010 | 于谦 | 58.25 |
| 013 | 蒋欣 | 61.0 |
| 014 | 赵丽颖 | 48.0 |
| 015 | 刘亦菲 | 70.25 |
| 017 | 范冰冰 | 45.25 |
| 018 | 李冰冰 | 58.0 |
| 019 | 邓紫棋 | 59.333333333333336 |
| 020 | 宋丹丹 | 69.75 |
+------------+--------------+---------------------+--+
5.2.3 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select
s1.stu_id
,s1.course_id
,s1.course
from score s1
join score s2
on s1.stu_id = s2.stu_id
where s1.course_id != s2.course_id and s1.course = s2.course
;
- 结果
+------------+---------------+------------+--+
| s1.stu_id | s1.course_id | s1.course |
+------------+---------------+------------+--+
| 016 | 03 | 71 |
| 017 | 04 | 34 |
| 016 | 01 | 71 |
| 005 | 05 | 85 |
| 007 | 05 | 63 |
| 009 | 05 | 79 |
| 017 | 02 | 34 |
| 005 | 04 | 85 |
| 007 | 04 | 63 |
| 009 | 04 | 79 |
+------------+---------------+------------+--+
5.2.4 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
select
s1.stu_id
from (
select
stu_id
,course
from score
where course_id="01"
) s1
join (
select
stu_id
,course
from score
where course_id="02"
) s2
on s1.stu_id = s2.stu_id
where s1.course > s2.course
;
- 结果
+------------+--+
| s1.stu_id |
+------------+--+
| 001 |
| 005 |
| 008 |
| 010 |
| 011 |
| 013 |
| 014 |
| 015 |
| 017 |
| 019 |
| 020 |
+------------+--+
5.2.5 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
select
ss1.stu_id
,s3.stu_name
from student s3
join (
select
s1.stu_id
from score s1, score s2
where s1.stu_id = s2.stu_id
and s1.course_id = "01"
and s2.course_id = "02"
) ss1
on s3.stu_id = ss1.stu_id
;
- 结果
+-------------+--------------+--+
| ss1.stu_id | s3.stu_name |
+-------------+--------------+--+
| 001 | 彭于晏 |
| 002 | 胡歌 |
| 004 | 刘德华 |
| 005 | 唐国强 |
| 006 | 陈道明 |
| 007 | 陈坤 |
| 008 | 吴京 |
| 009 | 郭德纲 |
| 010 | 于谦 |
| 011 | 潘长江 |
| 012 | 杨紫 |
| 013 | 蒋欣 |
| 014 | 赵丽颖 |
| 015 | 刘亦菲 |
| 016 | 周冬雨 |
| 017 | 范冰冰 |
| 018 | 李冰冰 |
| 019 | 邓紫棋 |
| 020 | 宋丹丹 |
+-------------+--------------+--+
5.2.6 查询学过“李体音”老师所教的所有课的同学的学号、姓名
select
sss1.stu_id
,s2.stu_name
from student s2
join (
-- 按学号分组，统计学过李老师所有课程的学生学号
select
ss1.stu_id
from (
-- 关联score、course、teacher三张表，查找到学过李体音老师所教的课程的学生
select
s1.stu_id
,s1.course_id
from teacher t1
join course c1
on t1.tea_id = c1.tea_id
join score s1
on s1.course_id = c1.course_id
where t1.tea_name = "李体音"
) ss1
group by ss1.stu_id
having count(ss1.course_id) = 2
) sss1
on sss1.stu_id = s2.stu_id
;
;
- 结果
+--------------+--------------+--+
| sss1.stu_id | s2.stu_name |
+--------------+--------------+--+
| 005 | 唐国强 |
| 007 | 陈坤 |
| 009 | 郭德纲 |
+--------------+--------------+--+
5.2.7 查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名
select
ss1.stu_id
,ss2.stu_name
from student ss2
join (
select
distinct(s1.stu_id) stu_id
from teacher t1
join course c1
on t1.tea_id = c1.tea_id
join score s1
on s1.course_id = c1.course_id
where t1.tea_name = "李体音"
) ss1
on ss1.stu_id = ss2.stu_id
;
- 结果
+-------------+---------------+--+
| ss1.stu_id | ss2.stu_name |
+-------------+---------------+--+
| 001 | 彭于晏 |
| 002 | 胡歌 |
| 004 | 刘德华 |
| 005 | 唐国强 |
| 007 | 陈坤 |
| 009 | 郭德纲 |
| 010 | 于谦 |
| 013 | 蒋欣 |
| 014 | 赵丽颖 |
| 015 | 刘亦菲 |
| 016 | 周冬雨 |
| 017 | 范冰冰 |
| 018 | 李冰冰 |
| 020 | 宋丹丹 |
+-------------+---------------+--+
5.2.8 查询选修“李体音”老师所授课程的学生中成绩最高的学生姓名及其成绩（与上题类似,用成绩排名，用 limit 1得出最高一个）
select
ss2.stu_id
,ss1.stu_name
from student ss1
join (
select
s1.stu_id
,s1.course
from teacher t1
join course c1
on t1.tea_id = c1.tea_id
join score s1
on s1.course_id = c1.course_id
where t1.tea_name = "李体音"
order by s1.course desc
limit 1
) ss2
on ss1.stu_id = ss2.stu_id
;
- 结果
+-------------+---------------+--+
| ss2.stu_id | ss1.stu_name |
+-------------+---------------+--+
| 002 | 胡歌 |
+-------------+---------------+--+
5.2.9 查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名
select
ss2.stu_id
,ss1.stu_name
from student ss1
join (
select
distinct(s1.stu_id) stu_id
from score s1
right join (
select
course_id
from score
where stu_id = "001"
) s2
on s1.course_id = s2.course_id
where s1.stu_id!="001"
) ss2
on ss1.stu_id = ss2.stu_id
;
- 结果
+-------------+---------------+--+
| ss2.stu_id | ss1.stu_name |
+-------------+---------------+--+
| 002 | 胡歌 |
| 004 | 刘德华 |
| 005 | 唐国强 |
| 006 | 陈道明 |
| 007 | 陈坤 |
| 008 | 吴京 |
| 009 | 郭德纲 |
| 010 | 于谦 |
| 011 | 潘长江 |
| 012 | 杨紫 |
| 013 | 蒋欣 |
| 014 | 赵丽颖 |
| 015 | 刘亦菲 |
| 016 | 周冬雨 |
| 017 | 范冰冰 |
| 018 | 李冰冰 |
| 019 | 邓紫棋 |
| 020 | 宋丹丹 |
+-------------+---------------+--+
5.2.10 查询所学课程与学号为“001”的学生所学课程完全相同的学生的学号和姓名
select
t2.stu_id,
t2.stu_name
from (
select
stu_id,
concat_ws('-',collect_set(course_id)) flage
from score
where stu_id='001'
group by stu_id
) t1
join (
-- 按照学生id和姓名分组，将学生学过的所有课程id拼接成字符串
select
s1.stu_id,
s.stu_name,
concat_ws('-',collect_set(s1.course_id)) flage
from score s1
join student s
on s1.stu_id = s.stu_id
where s.stu_id!='001'
group by s1.stu_id,s.stu_name
) t2
on t1.flage = t2.flage;
- 结果
+------------+--------------+--+
| t2.stu_id | t2.stu_name |
+------------+--------------+--+
| 002 | 胡歌 |
| 004 | 刘德华 |
| 010 | 于谦 |
| 013 | 蒋欣 |
| 014 | 赵丽颖 |
| 016 | 周冬雨 |
| 017 | 范冰冰 |
| 020 | 宋丹丹 |
+------------+--------------+--+
5.2.11 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select
ss1.stu_id
,ss2.stu_name
,cc1.course_name
,ss1.avg_course
from (
select
s1.stu_id
,s1.course_id
,s1.course
,s2.avg_course
from score s1
join (
select
stu_id
,avg(course) avg_course
from score
group by stu_id
) s2
on s1.stu_id=s2.stu_id
) ss1
join student ss2
on ss1.stu_id=ss2.stu_id
join course cc1
on ss1.course_id=cc1.course_id
order by ss1.avg_course desc
;
- 结果
-- 部分结果展示
+-------------+---------------+------------------+---------------------+
--+| ss1.stu_id | ss2.stu_name | cc1.course_name | ss1.avg_course |
+-------------+---------------+------------------+---------------------+
--+| 002 | 胡歌 | 数学 | 86.25
|| 002 | 胡歌 | 英语 | 86.25
|| 002 | 胡歌 | 体育 | 86.25
|| 002 | 胡歌 | 语文 | 86.25
|| 004 | 刘德华 | 体育 | 81.5
|| 004 | 刘德华 | 英语 | 81.5
|| 004 | 刘德华 | 数学 | 81.5
|| 004 | 刘德华 | 语文 | 81.5
|| 016 | 周冬雨 | 数学 | 81.25
|| 016 | 周冬雨 | 语文 | 81.25
|| 016 | 周冬雨 | 体育 | 81.25
|| 016 | 周冬雨 | 英语 | 81.25
|| 005 | 唐国强 | 体育 | 75.4
|| 005 | 唐国强 | 音乐 | 75.4
|| 005 | 唐国强 | 语文 | 75.4
|| 005 | 唐国强 | 数学 | 75.4
|| 005 | 唐国强 | 英语 | 75.4
|| 009 | 郭德纲 | 英语 | 74.2
|| 009 | 郭德纲 | 体育 | 74.2
|| 009 | 郭德纲 | 音乐 | 74.2
|| 009 | 郭德纲 | 语文 | 74.2
|| 009 | 郭德纲 | 数学 | 74.2
|+-------------+---------------+------------------+---------------------+
第流章 窗口查询
6.1 窗口的使用
6.1.1 查询每个学生的信息及平均成绩和其名次
select
s2.stu_id
,s2.stu_name
,s2.birthday
,s2.sex
,s1.course_avg
,rank() over(order by s1.course_avg desc) rk
from (
select
s.stu_id
,avg(s.course) course_avg
from score s
group by s.stu_id
) s1
right join student s2
on s1.stu_id = s2.stu_id
;
- 结果
002 胡歌 1994-03-20 男 86.25 1
004 刘德华 1998-08-28 男 81.5 2
016 周冬雨 1990-06-18 女 81.25 3
005 唐国强 1993-09-10 男 75.4 4
009 郭德纲 1992-12-05 男 74.2 5
006 陈道明 1992-11-12 男 73.333 3333333333 6
001 彭于晏 1995-05-16 男 72.5 7
015 刘亦菲 1993-01-14 女 70.25 8
020 宋丹丹 1991-03-01 女 69.75 9
013 蒋欣 1997-11-08 女 61.0 10
011 潘长江 1995-05-27 男 60.0 11
012 杨紫 1996-12-21 女 60.0 11
007 陈坤 1999-04-09 男 59.8 13
019 邓紫棋 1994-08-31 女 59.333333333333336 14
010 于谦 1998-08-23 男 58.25 15
018 李冰冰 1993-09-24 女 58.0 16
014 赵丽颖 1990-01-09 女 48.0 17
017 范冰冰 1992-07-04 女 45.25 18
008 吴京 1994-02-06 男 43.0 19
003 周杰伦 1995-04-30 男 NULL 20
Time taken: 145.454 se nds, Fetched: 20 row(s)
6.1.2 统计每个学科每个学生的成绩及排名
select
c1.course_name
,s2.stu_name
,s1.course
,rank() over(partition by s1.course_id order by s1.course desc) rk
from score s1
join student s2
on s1.stu_id = s2.stu_id
join course c1
on s1.course_id = c1.course_id
;
- 结果
-- 结果部分展示
+-----------------+--------------+------------+-----+--+
| c1.course_name | s2.stu_name | s1.course | rk |
+-----------------+--------------+------------+-----+--+
| 语文 | 彭于晏 | 94 | 1 |
| 语文 | 刘亦菲 | 90 | 2 |
| 语文 | 宋丹丹 | 89 | 3 |
| 语文 | 刘德华 | 85 | 4 |
| 语文 | 于谦 | 84 | 5 |
| 语文 | 赵丽颖 | 81 | 6 |
| 语文 | 郭德纲 | 75 | 7 |
| 语文 | 胡歌 | 74 | 8 |
| 语文 | 陈道明 | 71 | 9 |
| 语文 | 周冬雨 | 71 | 9 |
| 语文 | 唐国强 | 64 | 11 |
| 语文 | 潘长江 | 61 | 12 |
| 语文 | 范冰冰 | 58 | 13 |
| 语文 | 吴京 | 56 | 14 |
| 语文 | 陈坤 | 48 | 15 |
| 语文 | 蒋欣 | 47 | 16 |
| 语文 | 邓紫棋 | 46 | 17 |
| 语文 | 杨紫 | 44 | 18 |
| 语文 | 李冰冰 | 38 | 19 |
+-----------------+--------------+------------+-----+--+
74 rows selected (7.024 seconds)
6.1.3 统计每门学科的排名前2的学生
select
ss1.course_name
,ss1.stu_name
,ss1.course
,ss1.rk
from (
select
c1.course_name
,s2.stu_name
,s1.course
,rank() over(partition by s1.course_id order by s1.course desc) rk
from score s1
join student s2
on s1.stu_id = s2.stu_id
join course c1
on s1.course_id = c1.course_id
) ss1
where ss1.rk <= 2
;
- 结果
+------------------+---------------+-------------+---------+--+
| ss1.course_name | ss1.stu_name | ss1.course | ss1.rk |
+------------------+---------------+-------------+---------+--+
| 语文 | 彭于晏 | 94 | 1 |
| 语文 | 刘亦菲 | 90 | 2 |
| 数学 | 刘德华 | 93 | 1 |
| 数学 | 陈道明 | 90 | 2 |
| 英语 | 唐国强 | 99 | 1 |
| 英语 | 邓紫棋 | 93 | 2 |
| 英语 | 蒋欣 | 93 | 2 |
| 体育 | 胡歌 | 100 | 1 |
| 体育 | 周冬雨 | 94 | 2 |
| 音乐 | 李冰冰 | 87 | 1 |
| 音乐 | 唐国强 | 85 | 2 |
+------------------+---------------+-------------+---------+--+
6.1.4 查询每门课程的成绩第2名到第3名的学生姓名、授课老师及该课程信息及排名
select
ss1.stu_name
,ss1.tea_name
,ss1.course_name
,ss1.course
,ss1.rk
from (
select
s2.stu_name
,t1.tea_name
,c1.course_name
,s1.course
,rank() over(partition by s1.course_id order by s1.course desc) rk
from score s1
join student s2
on s1.stu_id = s2.stu_id
join course c1
on s1.course_id = c1.course_id
join teacher t1
on c1.tea_id = t1.tea_id
) ss1
where ss1.rk >=2 and ss1.rk <= 3
;
- 结果
+---------------+---------------+------------------+-------------+---------+--+
| ss1.stu_name | ss1.tea_name | ss1.course_name | ss1.course | ss1.rk |
+---------------+---------------+------------------+-------------+---------+--+
| 刘亦菲 | 王子文 | 语文 | 90 | 2 |
| 宋丹丹 | 王子文 | 语文 | 89 | 3 |
| 陈道明 | 张高数 | 数学 | 90 | 2 |
| 周冬雨 | 张高数 | 数学 | 89 | 3 |
| 邓紫棋 | 刘丽英 | 英语 | 93 | 2 |
| 蒋欣 | 刘丽英 | 英语 | 93 | 2 |
| 周冬雨 | 李体音 | 体育 | 94 | 2 |
| 唐国强 | 李体音 | 体育 | 85 | 3 |
| 唐国强 | 李体音 | 音乐 | 85 | 2 |
| 郭德纲 | 李体音 | 音乐 | 79 | 3 |
+---------------+---------------+------------------+-------------+---------+--+
6.1.5 查询各科成绩前三名的记录（如果有并列，则全部展示，例如如果前7名为：80,80,80,79,79,77,75,70，则统计结果为数字的前三名，结果为80,80,80,79,79,77）
select
ss1.course_name
,ss1.stu_name
,ss1.course
,ss1.rk
from (
select
c1.course_name
,s2.stu_name
,s1.course
,dense_rank() over(partition by s1.course_id order by s1.course desc) rk
from score s1
join student s2
on s1.stu_id = s2.stu_id
join course c1
on s1.course_id = c1.course_id
) ss1
where ss1.rk <= 3
;
;
- 结果
+------------------+---------------+-------------+---------+--+
| ss1.course_name | ss1.stu_name | ss1.course | ss1.rk |
+------------------+---------------+-------------+---------+--+
| 语文 | 彭于晏 | 94 | 1 |
| 语文 | 刘亦菲 | 90 | 2 |
| 语文 | 宋丹丹 | 89 | 3 |
| 数学 | 刘德华 | 93 | 1 |
| 数学 | 陈道明 | 90 | 2 |
| 数学 | 周冬雨 | 89 | 3 |
| 英语 | 唐国强 | 99 | 1 |
| 英语 | 邓紫棋 | 93 | 2 |
| 英语 | 蒋欣 | 93 | 2 |
| 英语 | 刘德华 | 89 | 3 |
| 体育 | 胡歌 | 100 | 1 |
| 体育 | 周冬雨 | 94 | 2 |
| 体育 | 唐国强 | 85 | 3 |
| 音乐 | 李冰冰 | 87 | 1 |
| 音乐 | 唐国强 | 85 | 2 |
| 音乐 | 郭德纲 | 79 | 3 |
+------------------+---------------+-------------+---------+--+
