概述
建表与数据导入
create database if not exists sql50;
use sql50;
//创建外部表(否则默认为内部表) 指定分隔符
create external table teacher(tid int,tname string) row format delimited fields terminated by 't';
create external table student(sid int,sname string,sage string,ssex string)row format delimited fields terminated by 't';
create external table sc(sid int,cid int,score int)partitioned by (month string)row format delimited fields terminated by 't';
create external table course(cid int,cname string,tid int)row format delimited fields terminated by 't';
load data local inpath '/export/sql50_data/student.csv' into table student;
load data local inpath '/export/sql50_data/techer.csv' into table teacher;
load data local inpath '/export/sql50_data/course.csv' into table course;
load data local inpath '/export/sql50_data/score.csv' into table sc;
设置hive本地模式运行
set hive.exec.mode.local.auto=true;
student.csv
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 女techer.csv
01 张三
02 李四
03 王五course.csv
01 语文 02
02 数学 01
03 英语 03score.csv
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 98SQL
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT s.*,sc1.score ,sc2.score
FROM student s,sc sc1,sc sc2
WHERE s.sid=sc1.sid AND s.sid=sc2.sid
AND sc1.cid='01' AND sc2.cid='02'
AND sc1.score>sc2.score
需要考虑01与02课程不同时存在,适合用外连接,不适合使用内连接,因为内连接会去掉空值
SELECT * FROM student s
LEFT JOIN sc sc1 ON s.sid=sc1.sid AND sc1.cid='01'
LEFT JOIN sc sc2 ON s.sid=sc2.sid AND sc2.cid='02'
WHERE nvl(sc1.score,0) > nvl(sc2.score,0)
-- nvl(sc1.score,0) 如果 sc1.score 值为NULL就返回 0,否则返回 sc1.score
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
方式一
select sc.sid,avg(sc.score) avg from sc group by sc.sid having avg>=60
select s.sid,s.sname,a.avg from student s join (select sid,avg(sc.score) avg from sc group by sc.sid having avg>=60) a on s.sid = a.sid
方式二
select *,avg(score) over(partition by sid) avg from sc
select sid,round(avg(score) over(partition by sid),2) avg from sc
select distinct sid,round(avg(score) over(partition by sid),2) avg from sc
select s.sid,s.sname,a.avg from student s,() a where s.sid=a.sid and a.avg>=60
select s.sid,s.sname,a.avg from student s,(select distinct sid,round(avg(score) over(partition by sid),2) avg from sc) a where s.sid=a.sid and a.avg>=60
select s.sid,s.sname,a.avg from student s,(select sid,avg from (select sid,round(avg(score) over(partition by sid),2) avg from sc )t group by t.sid,t.avg) a where s.sid=a.sid and a.avg>=60
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.sid,s.sname,count(sc.cid),sum(sc.score) from student s join sc sc on s.sid=sc.sid group by s.sid ,s.sname
select distinct sc.sid,count(sc.sid) over(partition by sc.sid),sum(sc.score) over(partition by sc.sid) from student s join sc sc on s.sid=sc.sid
6、查询"李"姓老师的数量
select count(*) from teacher t where t.tname like "李%"
7、查询学过"张三"老师授课的同学的信息
select * from sc sc join course c on sc.cid=c.cid join teacher t on c.tid=t.tid and t.tname="张三"
8、查询没学过"张三"老师授课的同学的信息
select sc.sid sid from sc sc join course c on sc.cid=c.cid join teacher t on c.tid=t.tid and t.tname="张三"
select * from sc where sc.sid not in ()
select * from sc where sc.sid not in (select sc.sid sid from sc sc join course c on sc.cid=c.cid join teacher t on c.tid=t.tid and t.tname="张三")
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
方式一
并行:利用学生ID相等关联两张表 然后分别给不同的条件
select * from sc sc1,sc sc2 where sc1.sid = sc2.sid and sc1.cid="01" and sc2.cid="02"
select sc1.sid from sc sc1,sc sc2 where sc1.sid = sc2.sid and sc1.cid="01" and sc2.cid="02"
select * from student s where s.sid in ()
select * from student s where s.sid in (select sc1.sid from sc sc1,sc sc2 where sc1.sid = sc2.sid and sc1.cid="01" and sc2.cid="02")
方式二
串行:先查出学过01课程的学生 然后到这个结果集中查学过02课程的学生
select sid from sc where sc.cid ="01"
select * from sc sc,() a where sc.sid = a.sid and sc.cid="02"
select * from sc sc,(select sid from sc where sc.cid ="01") a where sc.sid = a.sid and sc.cid="02"
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select sid from sc where sc.cid ="01"
select sid from sc where sc.cid ="02"
select sid from sc where sc.cid ="01" and sc.sid not in()
select sid from sc where sc.cid ="01" and sc.sid not in(select sid from sc s where s.cid ="02")
select sid from sc sc where sc.cid ="01" and not exists(select 1 from sc s where s.sid=sc.sid and s.cid ="02")
hive
11、查询没有学全所有课程的同学的信息
在成绩表中查询课程总数小于总课程的同学
select sid from sc group by sc.sid having count(cid) <3
关联学生表 显示相关信息
方式一:in子查询
Total jobs = 3
number of mappers: 1; number of reducers: 1
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0
select * from student s where s.sid in ()
select * from student s where s.sid in (select sid from sc group by sc.sid having count(cid) <3)
方式二:join连接查询
Total jobs = 2
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0
select * from student s join () a on s.sid=a.sid
select * from student s join (select sid from sc group by sc.sid having count(*) <3) a on s.sid=a.sid
方式三:where 提前
Total jobs = 1
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
select s.sid from student s join sc sc where s.sid = sc.sid group by s.sid having count(sc.cid)<3
explain select s.sid from student s join sc sc where s.sid = sc.sid group by s.sid having count(sc.cid)<3
select * from (select sid,count(cid) count from sc group by sid) a , (select count(cid) count from course) b where a.count<b.count
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
思路
1查询01号学生所学课程ID
2在成绩表中查找课程ID与学号为"01"的同学所学相同的同学的信息
1查询01号学生所学课程ID
select cid from sc where sid='01'
2在成绩表中查找课程ID至少有一门课与学号为"01"的同学所学相同的同学的信息
select b.sid from sc b where cid in (select a.cid from sc a where a.sid='01') group by b.sid
select * from student s join () c where s.sid=c.sid
select * from student s join (select b.sid sid from sc b where b.cid in (select a.cid from sc a where a.sid='01') group by b.sid) c on s.sid=c.sid
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select sid,cid from sc where sid='01'
select count(cid) count from sc where sid='01'
select sid,cid from sc where sid!='01'
select sid,count(cid) count from sc where sid!='01' group by sid
select * from ()a join ()b on join ()c on join() d on
select a.sid from
(select sid,count(cid) count from sc where sid!='01' group by sid) a
join (select count(cid) count from sc where sid='01') b on a.count =b.count
join (select sid,cid from sc where sid!='01') c on a.sid=c.sid
join(select sid,cid from sc where sid='01') d on c.cid=d.cid
group by a.sid
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select sid,cid from sc where sid='01'
select count(cid) count from sc where sid='01'
select sid,cid from sc where sid!='01'
select sid,count(cid) count from sc where sid!='01' group by sid
select * from ()a join ()b on join ()c on join() d on
select a.sid from
(select sid,count(cid) count from sc where sid!='01' group by sid) a
join (select count(cid) count from sc where sid='01') b on a.count =b.count
join (select sid,cid from sc where sid!='01') c on a.sid=c.sid
join(select sid,cid from sc where sid='01') d on c.cid=d.cid
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
查询学过张三老师课程的学生ID
select sc.sid from sc sc,course c ,teacher t where sc.cid = c.cid and c.tid = t.tid and t.tname ='张三'
查询没学过张三老师课程的学生
select s.* from student s where s.sid not in ()
select s.* from student s where s.sid not in (select sc.sid from sc sc,course c ,teacher t where sc.cid = c.cid and c.tid = t.tid and t.tname ='张三')
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
查询两门及其以上不及格课程的同学
查询满足上不条件的学生学号，姓名及其平均成绩
select s.sname,sc.sid,avg(sc.score) avg from student s,sc sc,(select sid from sc sc where sc.score<'60' group by sc.sid having count(sid) >= 2) a where s.sid =sc.sid and sc.sid=a.sid
group by sc.sid,s.sname
16、检索"01"课程分数小于60，按分数降序排列的学生信息
检索"01"课程分数小于60的学生
显示相关学生信息
检索"01"课程分数小于60的学生
select * from sc s where s.cid ='01' and s.score < 60 order by s.score desc
显示相关学生信息
select * from student a join () b on a.sid = b.sid order by b.score desc
select a.sid sid,a.sname name,b.score score from student a join (select s.sid sid,s.score score from sc s where s.cid ='01' and s.score < 60) b on a.sid = b.sid order by score desc
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select * from sc s join course c on s.cid = c.cid
列转行 语文 数学 英语
select s.sid sid ,
max(case when c.cname='语文' then s.score else null end) `语文`,
max(case when c.cname='数学' then s.score else null end) `数学`,
max(case when c.cname='英语' then s.score else null end) `英语`,
avg(s.score) `平均成绩`
from sc s join course c on s.cid = c.cid group by s.sid
18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select * from sc s join course c on s.cid=c.cid group by s.cid
select c.cid cid,c.cname name,
max(s.score) max,
min(s.score) min,
avg(s.score) avg,
sum(case when s.score>=60 then 1 else 0 end)/count(1) `及格率`,
sum(case when s.score>=70 and s.score<80 then 1 else 0 end)/count(1) `中等率`,
sum(case when s.score>=80 and s.score<90 then 1 else 0 end)/count(1) `优良率`,
sum(case when s.score>=90 then 1 else 0 end)/count(1) `优秀率`
from sc s join course c on s.cid=c.cid group by c.cid,c.cname
select c.cid cid,c.cname name,
max(s.score) max,
min(s.score) min,
bround(avg(s.score),2) avg,
bround(sum(case when s.score>=60 then 1 else 0 end)/count(1)*100,2) `及格率`,
bround(sum(case when s.score>=70 and s.score<80 then 1 else 0 end)/count(1)*100,2) `中等率`,
bround(sum(case when s.score>=80 and s.score<90 then 1 else 0 end)/count(1)*100,2) `优良率`,
bround(sum(case when s.score>=90 then 1 else 0 end)/count(1)*100,2) `优秀率`
from sc s join course c on s.cid=c.cid group by c.cid,c.cname
19、按各科成绩进行排序，并显示排名
select b.cid id,b.cname name,a.score score,
row_number() over(partition by a.cid order by a.score desc) rank
from sc a join course b on a.cid = b.cid
select b.cid id,b.cname name,a.score score,
rank() over(partition by a.cid order by a.score desc) rank
from sc a join course b on a.cid = b.cid
select b.cid id,b.cname name,a.score score,
dense_rank() over(partition by a.cid order by a.score desc) rank
from sc a join course b on a.cid = b.cid
20、查询学生的总成绩并进行排名
计算总成绩
排名
计算总成绩
select a.sid sid,sum(a.score) tatal from sc a group by a.sid order by tatal desc
排名
select *,row_number() over(order by b.tatal desc) rank from () b
select *,row_number() over(order by b.tatal desc) rank from (select a.sid sid,sum(a.score) tatal from sc a group by a.sid) b
21、查询不同老师所教不同课程平均分从高到低显示
select c.tname tname,b.cname cname,bround(avg(a.score) ,2) avg
from sc a join course b on a.cid=b.cid join teacher c on b.tid = c.tid
group by c.tname,b.cname
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
1产生排名 ==>t1
2查询第二名到第三名的成绩 ==>t2
3关联学生表,课程表与t2显示相关学生信息
1产生排名 使用窗口函数 ==>t1
select *,(row_number() over(partition by cid order by score desc)) rank from sc
2查询第二名到第三名的成绩 ==>t2
select * from () t1 where rank between 2 and 3
SELECT
*
FROM
( SELECT *,( row_number () over ( PARTITION BY cid ORDER BY score DESC )) rank FROM sc ) t1
WHERE
rank BETWEEN 2
AND 3
3关联学生表,课程表与t2显示相关学生信息
SELECT
s.sname,
c.cname,
t2.*
FROM
student s,
course c,(
SELECT
*
FROM
( SELECT *,( row_number () over ( PARTITION BY cid ORDER BY score DESC )) rank FROM sc ) t1
WHERE
rank BETWEEN 2
AND 3
) t2
WHERE
s.sid = t2.sid
AND c.cid = t2.cid
ORDER BY
t2.cid,
t2.rank
23、统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比
思路
1.观察表结构需要新增多列,先统计各分段人数
2.再算出所占百分比
方式二(横向)
1.先统计各分段人数
select sc.* from sc group by sc.cid
select *,
sum(case when sc.score >= 85 then 1 else 0 end) `100-85`
from sc group by sc.cid
select sc.cid,
sum(case when sc.score >= 85 then 1 else 0 end) `85-100`,
sum(case when sc.score >= 70 and sc.score < 85 then 1 else 0 end) `70-85`,
sum(case when sc.score >= 60 and sc.score < 70 then 1 else 0 end) `60-70`,
sum(case when sc.score < 60 then 1 else 0 end) `0-60`
from sc
group by sc.cid
select sc.cid `课程编号`,course.cname `课程名称`,
sum(case when sc.score >= 85 then 1 else 0 end) `85-100人数`,
sum(case when sc.score >= 70 and sc.score < 85 then 1 else 0 end) `70-85人数`,
sum(case when sc.score >= 60 and sc.score < 70 then 1 else 0 end) `60-70人数`,
sum(case when sc.score < 60 then 1 else 0 end) `0-60人数`
from sc left join course on sc.cid = course.cid
group by sc.cid,course.cname
2.再算出所占百分比
bround(DOUBLE a, INT d) Returns a rounded to d decimal places using HALF_EVEN rounding mode (as of Hive 1.3.0, 2.0.0). Example: bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4.
银行家舍入法,保留d位小数
bround(, 2)
bround(sum(case when sc.score >= 70 and sc.score <= 85 then 1 else 0 end), 2)
select sc.cid `课程编号`,course.cname `课程名称`,
sum(case when sc.score >= 85 then 1 else 0 end) `85-100人数`,
bround(sum(case when sc.score >= 85 then 1 else 0 end)/count(sc.score)*100, 2) `85-100百分比`,
bround(sum(case when sc.score >= 70 and sc.score < 85 then 1 else 0 end), 2) `70-85人数`,
bround(sum(case when sc.score >= 70 and sc.score < 85 then 1 else 0 end)/count(sc.score)*100, 2) `70-85百分比`,
sum(case when sc.score >= 60 and sc.score < 70 then 1 else 0 end) `60-70人数`,
bround(sum(case when sc.score >= 60 and sc.score < 70 then 1 else 0 end)/count(sc.score)*100, 2) `60-70百分比`,
sum(case when sc.score < 60 then 1 else 0 end) `0-60人数`,
bround(sum(case when sc.score < 60 then 1 else 0 end)/count(sc.score)*100, 2) `0-60百分比`
from sc left join course on sc.cid = course.cid
group by sc.cid,course.cname
24、查询学生平均成绩及其名次
1.统计平均成绩=>t1
2.排名
3.关联学生表,显示相关信息
1.统计平均成绩=>t1
select *,(avg(sc.score) over(partition by sc.sid)) avg from sc
select sid,bround((avg(sc.score) over(partition by sc.sid)), 2) avg from sc
2.排名
select *,() rank from () t1
SELECT
*,(row_number () over ( ORDER BY avg DESC )) rank
FROM
( SELECT sid, bround (( avg( sc.score ) over ( PARTITION BY sc.sid )), 2 ) avg FROM sc ) t1
3.关联学生表,显示相关信息
SELECT
s.sname NAME,
t1.sid id,
t1.avg,
(row_number () over ( ORDER BY avg DESC )) rank
FROM
( SELECT sid, bround (( avg( sc.score ) over ( PARTITION BY sc.sid )), 2 ) avg FROM sc ) t1
LEFT JOIN student s ON t1.sid = s.sid
25、查询各科成绩前三名的记录
思路
1排名 =>t1
2选出前三名
1排名 =>t1
select *,() rank from sc
select *,(row_number() over(partition by sc.cid order by sc.score desc)) rank from sc
2选出前三名
select * from () t1 where t1.rank<=3
26、查询每门课程被选修的学生数
select *,count(sc.sid) over(partition by sc.cid) from sc
27、查询出只有两门课程的全部学生的学号和姓名
思路
1统计学生选课数量=>t1
2找出只有两门课的学生
3关联
1统计学生选课数量=>t1
select *,count(sc.sid) over(partition by sc.sid) amount from sc
2找出只有两门课的学生
select * from () t1 where t1.amount = 2
select * from (select *,count(sc.sid) over(partition by sc.sid) amount from sc) t1 where t1.amount = 2
3关联
select s.sid,s.sname from (select *,count(sc.sid) over(partition by sc.sid) amount from sc) t1 left join student s
on t1.sid = s.sid and t1.amount = 2
-- left join会产生null值 改进
select s.sid,s.sname from (select *,count(sc.sid) over(partition by sc.sid) amount from sc) t1 inner join student s
on t1.sid = s.sid and t1.amount = 2
28、查询男生、女生人数
-- 28、查询男生、女生人数
-- 横向
select () `男生人数`,() `女生人数` from student
select sum(case when s.ssex='男' then 1 else 0 end) `男生人数`,sum(case when s.ssex='女' then 1 else 0 end) `女生人数` from student s
-- 纵向
select () `性别`,() `人数` from student s
select (case when s.ssex = '男' then '男' else '女' end) `性别`,count(s.ssex) `人数` from student s
group by (case when s.ssex = '男' then '男' else '女' end)
29、查询名字中含有"风"字的学生信息
select * from student s where s.sname like '%风%'
30、查询同名同性学生名单，并统计同名人数
-- 这里的1代表第一列,2代表第二列 直接写列名效果也是一样的
-- GROUP BY 1,2 的意思是先按第一列分组 第一列相同再按第二列分组 两列都相同就在同一组
-- 同名同姓人数
SELECT sname,ssex,count(sname)
FROM Student GROUP BY sname,ssex
HAVING count(sname)>1;
-- 同名人数
SELECT sname,count(sname)
FROM Student GROUP BY sname
HAVING count(sname)>1;
31、查询1990年出生的学生名单(注：Student表中Sage列的类型是string)
SELECT Student.* FROM Student WHERE YEAR(sage)=1990;
32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
统计每门课的平均成绩 按平均成绩排序
select cid,avg(sc.score) avg from sc group by sc.cid order by avg desc,cid
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
1统计每个学生的平均成绩 =>t1
2选出大于85的平均成绩 =>t2
3关联学生表,显示相关信息
1统计每个学生的平均成绩 =>t1
select sid,avg(sc.score) avg from sc group by sc.sid
2选出大于85的平均成绩 =>t2
select * from () t1 where t1.avg>=85
select * from (select sid,avg(sc.score) avg from sc group by sc.sid) t1 where t1.avg>=85
3关联学生表,显示相关信息
select * from () t2,student s where t2.sid = s.sid
select s.sid,s.sname,t2.avg from (select * from (select sid,avg(sc.score) avg from sc group by sc.sid) t1 where t1.avg>=85) t2,student s where t2.sid = s.sid
34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select s.sname,sc.score from course c,sc sc,student s where c.cid=sc.cid and sc.sid=s.sid and c.cname='数学' and sc.score < 60
35、查询所有学生的课程及分数情况；
select s.sname,c.cname,sc.score from course c,sc sc,student s where c.cid=sc.cid and sc.sid=s.sid
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
-- 如果将任何一门成绩在70分以上,理解为所有成绩在70分以上的话
思路
1.查出每个学生的最小的成绩 => t1
2.选出最小的成绩比70大的学生,那么此学生的所有成绩都大于70 => t2
3.关联其它表,显示相关信息
1.查出每个学生的最小的成绩
select sid,min(score) min_score from sc group by sid
2.选出最小的成绩比70大的学生,那么此学生的所有成绩都大于70
select sid from () t1 where t1.min_score>70
select sid from (select sid,min(score) min_score from sc group by sid) t1 where t1.min_score>70
3.关联其它表,显示相关信息
select * from student s,sc sc,course c,() t2 where s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid
select
s.sname `学生姓名`,c.cname `课程名`,sc.score `分数`
from
student s,sc sc,course c,(select sid from (select sid,min(score) min_score from sc group by sid) t1 where t1.min_score>70) t2
where
s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid
-- 如果将任何一门成绩在70分以上,理解为其中一门成绩在70分以上的话
思路
1.查出每个学生的最大的成绩 => t1
2.选出最大的成绩比70大的学生,那么此学生其中一门成绩大于70 => t2
3.关联其它表,显示相关信息
1.查出每个学生的最大的成绩
select sid,max(score) max_score from sc group by sid
2.选出最大的成绩比70大的学生,那么此学生其中一门成绩大于70 => t2
select sid from () t1 where t1.max_score>70
select sid from (select sid,max(score) max_score from sc group by sid) t1 where t1.max_score>70
3.关联其它表,显示相关信息
select * from student s,sc sc,course c,() t2 where s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid
select
s.sname `学生姓名`,c.cname `课程名`,sc.score `分数`
from
student s,sc sc,course c,(select sid from (select sid,max(score) max_score from sc group by sid) t1 where t1.max_score>70) t2
where
s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid
37、查询不及格的课程
select * from sc where sc.score<60
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select * from sc sc,student s where sc.sid=s.sid and sc.cid='01' and sc.score >= 80
39、求每门课程的学生人数
select count(sc.sid) from sc group by sc.cid
40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select *,max(sc.score) from sc sc,course c,teacher t where sc.cid=c.cid and c.tid=t.tid and t.tname='张三' group by t.tname
-- Expression not in GROUP BY key sid
select max(sc.score) from sc sc,course c,teacher t where sc.cid=c.cid and c.tid=t.tid and t.tname='张三' group by t.tname
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
课程成绩去重后的课程数小于总课程数则不同课程存在相同成绩
select * from (select *,count(sc.score) count ,count(distinct sc.score) distinct_count from sc group by sc.sid) t1 where distinct_count<count
Error: Error while compiling statement: FAILED: SemanticException [Error 10025]: Expression not in GROUP BY key cid
select * from (select sc.sid,count(sc.score) count ,count(distinct sc.score) distinct_count from sc group by sc.sid) t1 where distinct_count<count
42、查询每门功成绩最好的前两名
select *,() rank from sc
select *,(row_number() over(partition by sc.cid order by sc.score desc)) rank from sc
select * from () t1 where t1.rank<3
select * from (select *,(row_number() over(partition by sc.cid order by sc.score desc)) rank from sc) t1 where t1.rank<3
43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select sc.cid,count(sc.sid) count from sc group by sc.cid order by count desc,sc.cid
select * from ()t1 where t1.count>5
select * from (select sc.cid,count(sc.sid) count from sc group by sc.cid order by count desc,sc.cid)t1 where t1.count>5
-- ref
SELECT cid,count(1) count FROM SC GROUP BY cid HAVING count>=5 ORDER BY count desc,cid ;
44、检索至少选修两门课程的学生学号
select *,count(sc.cid) count from sc group by sc.sid
select * from () t1 where t1.count>=2
select * from (select sc.sid,count(sc.cid) count from sc group by sc.sid) t1 where t1.count>=2
45、查询选修了全部课程的学生信息
select *,count(sc.cid) count from sc group by sc.sid
select * from () t2,() t1 where t2.count=t1.count
select * from (select *,count(sc.cid) count from sc group by sc.sid ) t2,(select count(c.cid) count from course c) t1 where t2.count=t1.count
select * from (select sc.sid,count(sc.cid) count from sc group by sc.sid ) t2,(select count(c.cid) count from course c) t1 where t2.count=t1.count
46、查询各学生的年龄
SELECT Student.*,YEAR(current_date)-YEAR(Student.sage) FROM Student;
47、查询本周过生日的学生
SELECT * FROM Student WHERE WEEKOFYEAR(current_date)-WEEKOFYEAR(sage)=0;
48、查询下周过生日的学生
SELECT * FROM Student WHERE WEEKOFYEAR(current_date)-WEEKOFYEAR(sage)=-1;
49、查询本月过生日的学生
select * from student where month(current_date)-month(sage)=0;
50、查询下月过生日的学生
select * from student where month(current_date)-month(sage)=-1;
最后
以上就是欢喜绿草为你收集整理的经典SQL50练习题Hive版建表与数据导入SQL的全部内容，希望文章能够帮你解决经典SQL50练习题Hive版建表与数据导入SQL所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复