hive学习之经典sql50题 hive版(二)

1.查询“某1”课程比“某2”课程成绩高的所有学生的学号
select s.id
from
(
select s1.sid id,s1.score c1,s2.score c2
from (
select sid,score from sc where cid=1
) s1
join (
select sid,score from sc where cid=2
) s2 
on s1.sid=s2.sid
) s

where s.c1>s.c2;

之前写的水平太次了，新的如下:

SELECT sss.sid
FROM
(
SELECT sid sid,sum(c1) s1,sum(c2) s2
FROM
(
SELECT sid sid,
    case WHEN cid=1 THEN score ELSE -1 END c1,
    case WHEN cid=2 THEN score ELSE -1 END c2
FROM sc
) scc
WHERE c1!=-1 or c2!=-1
GROUP BY sid
) sss
WHERE s1!=-1 AND s2!=-1 AND s1>s2

 首先

SELECT sid sid,
    case WHEN cid=1 THEN score ELSE -1 END c1,
    case WHEN cid=2 THEN score ELSE -1 END c2
FROM sc

 

这样做为了，过滤课程1或课程2没成绩的学生，为什么-1呢？0的话，有可能有的学生成绩为0

SELECT sid sid,sum(c1) s1,sum(c2) s2
FROM
(
SELECT sid sid,
    case WHEN cid=1 THEN score ELSE -1 END c1,
    case WHEN cid=2 THEN score ELSE -1 END c2
FROM sc
) scc
WHERE c1!=-1 or c2!=-1
GROUP BY sid

 

到这，有一门没成绩的可以通过等于-1过滤，然后where  课程1成绩>课程2成绩 

 

2.查询平均成绩大于60分的同学的学号和平均成绩；

select sid,round(avg(score),1) from sc group by sid having avg(score)>60;

 

3.查询所有同学的学号、姓名、选课数、总成绩
select student.sid,student.sname,s.num,s.total
from
(
select sid id,count(cid) num,sum(score) total 
from sc group by sid
) s
join student
on s.id=student.sid;

 

4.查询姓“李”的老师的个数

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

 

5.查询没学过“张三”老师课的同学的学号、姓名
select notstudy.sid,student.sname
from
(
select all.sid sid
from
(
select distinct sid from sc 
) all
left join
(
select sid sid
from
(
select course.cid c
from
(
select tid from teacher where tname='张三'
) t
join course
on t.tid=course.tid
) c
join sc
on sc.cid=c.c
) s
on s.sid=all.sid
where s.sid is null
) notstudy
join student

on student.sid=notstudy.sid;

 

6.查询学过数学并且也学过编号语文课程的同学的学号、姓名
select ch.s
from
(
select sc.sid s
from
(
select cid from course where cname='语文'
) c
join sc
on c.cid=sc.cid
) ch
join
(
select sc.sid s
from
(
select cid from course where cname='数学'
) c
join sc
on c.cid=sc.cid
) ma

on ch.s=ma.s;

 

7.查询学过“张三”老师所教的所有课的同学的学号、姓名
select s.s sid,student.sname sname
from
(
select sid s
from
(
select course.cid c
from
(
select tid from teacher where tname='张三'
) t
join course
on t.tid=course.tid
) c
join sc
on sc.cid=c.c
) s
join student
on s.s=student.sid


8.查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名
select s.id
from
(
select s1.sid id,s1.score c1,s2.score c2
from (
select sid,score from sc where cid=1
) s1
join (
select sid,score from sc where cid=2
) s2 
on s1.sid=s2.sid
) s

where s.c1<s.c2;

 

9.查询所有课程成绩小于60分的同学的学号、姓名
select stu.sid,student.sname
from
(
select lt60.sid sid
from
(
select s.sid sid,count(s.sid) c
from
(
select sid from sc 
where score<=60
) s
group by s.sid
) lt60
join
(
select sid sid,count(1) c
from sc 
group by sid
) all
on lt60.sid=all.sid and lt60.c=all.c
) stu
join student
on stu.sid=student.sid;

 

10.查询没有学全所有课的同学的学号、姓名

select s.sid,student.sname
from
(
select sid from sc group by sid having count(1)<3
) s
join student
on s.sid=student.sid;