《SQL面试50题》刷题笔记 day2（知识点：左联结、分组函数group by）

问题1.1、1.2和1.3来源于一个网页，应该不是50题中的，是第一题的拆解问题。
数据表：

问题1.1 查询同时存在" 01 “课程和” 02 "课程的情况

SELECT a.sid FROM 
(SELECT * FROM score  WHERE cid = '01') AS a
INNER JOIN
(SELECT * FROM score  WHERE cid = '02') AS b
ON
a.sid = b.sid;

select t.sid 
from
(select sid,cid from score where cid in('01','02')) t
group by sid
having count(t.cid)=2;

select sid 
from 
(select sid, max(case cid when '01' then 1 else 0 end) c1,
			max(case cid when '02' then 1 else 0 end) c2
			from score
			group by sid
)t
where c1=c2 and c1=1;

sid
01
02
03
04
05

问题1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
题意理解：一定有01，可能有02，但一定没有03

select * from
(select * from score where cid = '01') a
left join score b
on a.sid = b.sid and b.cid not in ('01','03');

查询结果：

+-----+-----+--------+------+------+--------+
| sid | cid | sscore | sid  | cid  | sscore |
+-----+-----+--------+------+------+--------+
| 01  | 01  |     80 | 01   | 02   |     90 |
| 02  | 01  |     70 | 02   | 02   |     60 |
| 03  | 01  |     80 | 03   | 02   |     80 |
| 04  | 01  |     50 | 04   | 02   |     30 |
| 05  | 01  |     76 | 05   | 02   |     87 |
| 06  | 01  |     31 | NULL | NULL |   NULL |
+-----+-----+--------+------+------+--------+

SELECT * FROM 
(SELECT * FROM score  WHERE cid = '01') AS a
LEFT JOIN
(SELECT * FROM score  WHERE cid = '02') AS b
ON
a.sid = b.sid;-- LEFT JOIN左连接查询结果显示左表中所有数据

查询结果：

+-----+-----+--------+------+------+--------+
| sid | cid | sscore | sid  | cid  | sscore |
+-----+-----+--------+------+------+--------+
| 01  | 01  |     80 | 01   | 02   |     90 |
| 02  | 01  |     70 | 02   | 02   |     60 |
| 03  | 01  |     80 | 03   | 02   |     80 |
| 04  | 01  |     50 | 04   | 02   |     30 |
| 05  | 01  |     76 | 05   | 02   |     87 |
| 06  | 01  |     31 | NULL | NULL |   NULL |
+-----+-----+--------+------+------+--------+

tips:
一般见到不存在时候显示null的题目考虑用左右联结解决。

问题1.3 查询不存在" 01 “课程但存在” 02 "课程的情况

select sid 
from score
where cid='02' and sid not in
			(select sid from score where cid='01');

select sid 
from(
	select sid,max(case cid when '01' then score end) c1,
				max(case cid when '02' then score end) c2
	from score group by sid) t
where c1 is null and c2 is not null;

问题2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

方式1：

select sc.sid, s.sname, avg(sc.sscore) as avescore 
from student s 
join score sc 
on s.sid=sc.sid
GROUP BY sc.sid, s.sname
HAVING AVG(sc.sscore)>60;

sid sname  avescore
01	赵雷	89.6667
02	钱电	70.0000
03	孙风	80.0000
05	周梅	81.5000
07	郑竹	93.5000

方式2：用where和,的联结 加 子查询

select  s.sid, s.sname, a. avgscore from
student s,--，
(SELECT AVG(sc.sscore) as avgscore, sc.sid 
from score as sc 
group by sc.sid 
HAVING AVG(sc.sscore)>=60) a
where s.sid =a.sid;--用,和where联结
--表a中不给表score起别名也是可以正确运行的

方式2：用join联结 加 子查询

select a.sid,s.sname, a.avescore 
from
	(select sid, avg(sscore) as avescore 
	from score 
	group by sid 
	having avg(sscore)>=60) a
join student s on s.sid=a.sid;

where 置于group by 之前过滤行
having 置于group by 之后过滤分组

为了熟悉开窗函数，这里用聚集开窗函数试了一下

方式3：开窗函数

select a.sid, s.sname, a.avescore 
from (
	select distinct sid, avg(sscore) over(partition by sid) avescore 
	from score) a
join student s
on s.sid=a.sid and a.avescore>=60;