02.Hive必刷50题--6-10题

– 6、查询"李"姓老师的数量:

SELECT COUNT(*)
FROM teacher
WHERE t_name LIKE '李%'

加group和不加的区别：

在数据中加入一列，进行查询

select t_name,count(1) 
from teacher  
where t_name like '李%' 
group by t_name;

SELECT t_name,COUNT(*)
FROM teacher
WHERE t_name LIKE '李%'

COUNT(*)和COUNT(1)的区别？没有区别。查询效率一样

 

– 7、查询学过"张三"老师授课的同学的信息:

方式1：

SELECT s.*
FROM student s 
JOIN score sc on s.s_id=sc.s_id
JOIN course c on c.c_id=sc.c_id
JOIN teacher t on t.t_id=c.t_id AND t.t_name='张三'

方式2：

SELECT s.*
FROM teacher t 
JOIN course c on 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
WHERE t.t_name='张三'

– 8、查询没学过"张三"老师授课的同学的信息:

SELECT * 
FROM student s
LEFT JOIN 
(SELECT s_id
FROM score sc
JOIN course c ON sc.c_id=c.c_id
JOIN teacher t ON c.t_id=t.t_id AND t.t_name='张三') tmp
ON s.s_id=tmp.s_id
WHERE tmp.s_id is NULL

思路：

先找出上过张三老师课的学生id，和学生表进行join，之后没有s_id的记录就是我们要的没有上过张三老师课的学生

– 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息:

方式1：

SELECT s.*
FROM student s 
JOIN score sc1 ON s.s_id=sc1.s_id AND sc1.c_id='01'
JOIN score sc2 ON s.s_id=sc2.s_id AND sc2.c_id='02'

方式2：

SELECT * 
FROM student s
JOIN (SELECT sc1.s_id FROM score sc1 where sc1.c_id='01') tmp1 ON s.s_id=tmp1.s_id
JOIN (SELECT sc2.s_id FROM score sc2 where sc2.c_id='02') tmp2 ON s.s_id=tmp2.s_id

 

– 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息:

SELECT * 
FROM student s
JOIN (SELECT sc1.s_id FROM score sc1 where sc1.c_id='01') tmp1 ON s.s_id=tmp1.s_id
LEFT JOIN (SELECT sc2.s_id FROM score sc2 where sc2.c_id='02') tmp2 ON s.s_id=tmp2.s_id
WHERE tmp2.s_id IS NULL;

为什么在tmp1加了left join没有查询到06？

SELECT * 
FROM student s
LEFT JOIN (SELECT sc1.s_id FROM score sc1 where sc1.c_id='01') tmp1 ON s.s_id=tmp1.s_id
JOIN (SELECT sc2.s_id FROM score sc2 where sc2.c_id='02') tmp2 ON s.s_id=tmp2.s_id

 原因：查询tmp2的时候06的s_id不存在

SELECT * 
FROM student s
LEFT JOIN (SELECT sc1.s_id FROM score sc1 where sc1.c_id='01') tmp1 ON s.s_id=tmp1.s_id

 

后续部分参见:

00.Hive必刷50题--建表，插入数据

01.Hive必刷50题--1-5题

02.Hive必刷50题--6-10题

03.Hive必刷50题--11-15题

04.Hive必刷50题--16-20题

05.Hive必刷50题--21-25题

06.Hive必刷50题--26-30题

07.Hive必刷50题--31-35题

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

09.Hive必刷50题--41-45题

010.Hive必刷50题--46-50题