MySQL HIVE经典50题11~15题

初步考虑优化 优化job的数量 数量越少越好
– 11、查询没有学全所有课程的同学的信息
1
子查询 6个job
select s.* from
student s,
(
select sid from
(select count(cid) cid from course)b1,
(select count(cid) cid,sid from sc group by sid)b2
where b1.cid!=b2.cid
) b3
where s.sid=b3.sid;

6个job

感觉挺好 出来结果了 对了
但是回顾一下student表 还少了08
所以一开始的思路 就错了

select count(cid) cid,sid from student s left join sc on s.sid=sc.sid group by s.sid
如果你这样用 再套上面的 不就没利用完现在的表信息
2
not in 7个job
用了 not in 7个job
select sid from sc group by sid having count(cid) not in
(select count(cid) cid from course);
3
三表关联 join 只有一个job
多表连接
自然连接
select * from s,c
多表连接 2
select * from teacher t join course c on t.tid=c.tid left join sc on c.cid=sc.cid
join student s on s.sid=sc.sid;

多表连接3
select *
from student stu
join course a
left join sc b
on b.sid = stu.sid and b.cid = a.cid

运行完只有一个job

看上面 就知道 score is null
然后就想分组
那就错了 分组之后 就只能取分组字段
那怎么变成一个呢 用distinct 去重就可以了
select distinct stu.*
from student stu
join course a
left join sc b on b.sid = stu.sid and b.cid = a.cid
where b.score is null

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

一个job

select distinct stu.*
from student stu
join sc a on a.sid = stu.sid
where stu.sid <> ‘01’ and a.cid in (
select cid from sc where sid = ‘01’)
;

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

select distinct sid,sname,birth,sex from
(
select ,count(cid)over(partition by sid) rank
from(
select stu.,a.cid
from student stu
join sc a on a.sid = stu.sid
where stu.sid <> ‘01’ and a.cid in(
select cid from sc where sid = ‘01’)
)b1
)b2
,
(select count(cid) cid from course)b3
where b3.cid=b2.rank
;

6个job

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

select * from student s left join sc on s.sid=sc.sid
where sc.cid not in
(select cid from course c join teacher t on t.tid=c.tid where tname=‘张三’)

这样只能过滤掉02的cid

应该对sid 进行not in 或者 not exists

not exists 与not in
1字表与join连接的情况
优化

6个job
select s.*
from student s
where s.sid not in
(
select sc.sid from student s left join sc on s.sid=sc.sid join course c on sc.cid=c.cid join
teacher t on t.tid=c.tid and t.tname=‘张三’)
换用not exists
select s.*
from student s
where not exists
(
select sc.sid from sc where s.sid=sc.sid and sc join course c on sc.cid=c.cid join
teacher t on t.tid=c.tid and t.tname=‘张三’)
失败 not exists话
exists 中的语句与外表进行了关联 所以里面的语句应该执行不了
用了exists 就不能用 join 因为 join的话 与外表就没有任何关联了
not in 不需要与外表进行关联 里面语句可以直接运行

2 子表自然连接
3个job
select s.*
from student s
where not exists
(
select sc.sid from sc,teacher t,course c where sc.sid=s.sid and c.tid=t.tid and
c.cid=sc.cid and t.tname=‘张三’);

6个job
select *
from student stu
where stu.sid not in (
select c.sid
from course a,teacher b,sc c
where b.tname = ‘张三’ and a.tid = b.tid and c.sid = stu.sid and c.cid = a.cid
)
;

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

1个job

select s.sid,s.sname,avg(score) from student s left join sc on s.sid=sc.sid
group by s.sid,s.sname
having sum(case when score<60 then 1 else 0 end)>=2

如果考虑缺考或者退考的情况
准备另外一个sc表

准备另外的student表

三个job

select sid,sname,avg from
(
select *,avg(score)over(partition by sid) avg,
case when num=0 and score<60 then 1
when num=1 and score<60 then 1
when num=3 and score<60 then 0 else 0
end as l0
from
(
select s.sid,s.sname,nvl(score,0) score,3-count(score)over(partition by s.sid) num
from student s left join sc on s.sid=sc.sid
) b1
)b2
group by sid,sname,avg having (sum(l0)+max(num))>=2;

看后续