问题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;