08.Hive必刷50題--36-40題

– 36、查詢任何一門課程成績在70分以上的學生姓名、課程名稱和分數:

SELECT s.s_id,s.s_name,c.c_name,tmp2.s_score
FROM student s
JOIN 
(SELECT sc.*
FROM score sc 
LEFT JOIN (SELECT s_id FROM score WHERE s_score<70 GROUP BY s_id) tmp
ON sc.s_id=tmp.s_id
WHERE tmp.s_id is NULL
)tmp2
ON s.s_id=tmp2.s_id
JOIN course c ON tmp2.c_id=c.c_id

怎麼查每個學生的每個課程成績高於70？先查出有在70分以下的學生，之後連線score表，就能找出那些都在70分以上的

– 37、查詢課程不及格的學生:

SELECT s.s_name,tmp.*
FROM student s
JOIN
(SELECT sc.s_id,c_name,s_score
FROM score sc JOIN course c ON sc.c_id=c.c_id
WHERE s_score<60
)tmp 
ON tmp.s_id=s.s_id

–38、查詢課程編號為01且課程成績在80分以上的學生的學號和姓名:

SELECT s.s_id,s.s_name,sc.s_score
FROM score sc 
JOIN student s ON sc.s_id=s.s_id
WHERE c_id='01' AND s_score>=80

– 39、求每門課程的學生人數:

SELECT c.c_id,c.c_name,COUNT(1)
FROM score sc 
JOIN course c ON sc.c_id=c.c_id
GROUP BY c.c_id,c.c_name

– 40、查詢選修"張三"老師所授課程的學生中，成績最高的學生資訊及其成績:

SELECT tmp.*
FROM
(SELECT s.*,s_score,RANK() over(ORDER BY s_score DESC) ranking
FROM teacher t
JOIN course c ON t.t_name='張三' AND t.t_id=c.t_id
JOIN score sc ON c.c_id=sc.c_id 
JOIN student s ON sc.s_id=s.s_id) tmp
WHERE tmp.ranking=1