《SQL面试50题》刷题笔记 day13（ 知识点：各种小知识点）

继续刷简单题

问题25 查询各科成绩前三名的记录

select * 
from(
select cid, sscore, sid, 
			dense_rank() over(partition by cid order by sscore desc) rk
from score) s
where rk<=3;

(select cid,sid,sscore from score where cid = '01' order by sscore desc limit 3)
UNION
(select cid,sid,sscore from score where cid = '02' order by sscore desc limit 3)
UNION
(select cid,sid,sscore from score where cid = '03' order by sscore desc limit 3);

问题26 查询学生平均成绩及其名次

select sid,avg(sscore),row_number() over (order by avg(sscore) desc) as rank_score
from score
group by sid;

#实现函数row_number
SELECT q.sid,total,@crank:=@crank +1 as ran 
FROM
	(SELECT sc.sid,avg(sc.sscore) as total 
	FROM score sc 
	GROUP BY sc.sid 
	ORDER BY total DESC) as q, #用内部子查询先把平均值算出来，再进行外面的变量赋值计算
	(select @crank:=0) r; 

问题27 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

Select cid, avg(sscore) 
from score 
group by cid 
order by avg(sscore) desc, cid asc;

问题28 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

select sc.sid, s.sname, avg(sc.sscore) 
from student s, score sc
where s.sid =sc.sid
group by sc.sid,s.sname
having avg(sc.sscore)>=85;

问题29 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

Select s.sname, sc.sscore 
from student s
Inner join score sc on sc.sid=s.sid and sc.sscore<60
Inner join course c on c.cid=sc.cid and c.cname ='数学';
#运行结果：
#+--------+--------+
#| sname  | sscore |
#+--------+--------+
#| 李云   |     30 |
#+--------+--------+

问题30 查询所有学生的课程及分数情况

Select s.sid, s.sname, sc.cid, sc.sscore 
from student s 
left join score sc 
on s.sid=sc.sid;

问题31 查询任何一门课程成绩在70分以上的姓名、课程名称和分数

Select s.sname, c.cname, sc.sscore 
from student s, course c, score sc
Where s.sid =sc.sid and sc.cid=c.cid and sc.sscore>70;

问题32 查询存在不及格的课程

#方法1
Select distinct cid from score where sscore < 60;
#方法2
Select cid from score where sscore<60 group by cid;

问题33 查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名

Select sc.sid, s.sname 
from student s
Inner join score sc 
On s.sid = sc.sid and sc.cid ='01' and sscore >=80;

问题34 求每门课程的学生人数

Select cid, count(sid) from score group by cid;

问题35 查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
解法1：

Select s.sid, s.sname, s.sbirth, s.ssex, max(sscore) 
from student s, score sc, course c, teacher t
Where s.sid=sc.sid and sc.cid=c.cid and c.tid=t.tid and t.tname='张三';

这里有聚集函数但不用加group by，因为select执行顺序在where之后，已经只剩选择张三老师的课程的学生的成绩了，所以直接用max。
group by和聚集函数都是可以单独出现使用的。

select s.* ,ss.sscore
from student s, 
	 score ss, 
	 (select e.cid, max(e.sscore) max_score
	 from student s,score e,course c, teacher t
	 where s.sid=e.sid and e.cid=c.cid and c.tid=t.tid and t.tname='张三'
     group by e.cid) a   
where s.sid=ss.sid and a.cid=ss.cid and ss.sscore=a.max_score;

问题36 查询选修「李四」老师所授课程的学生中，成绩最高的学生信息及其成绩
解法1：

select a.sid , a.sname, a.sbirth, a.ssex, a.sscore from
(Select s.sid, s.sname, s.sbirth, s.ssex,sc.sscore from student s, score sc, course c, teacher t
Where s.sid=sc.sid and sc.cid=c.cid and c.tid=t.tid and t.tname='李四') a 
Inner join
(Select s.sid, s.sname, s.sbirth, s.ssex,max(sscore) as maxscore from student s, score sc, course c, teacher t
Where s.sid=sc.sid and sc.cid=c.cid and c.tid=t.tid and t.tname='李四') b
on a.sscore =b.maxscore;

也是先定下来李四老师这一组，所以虽然有聚集函数max，但是不用group by来分组
可以简化一下，看着太复杂了。。

运行结果：

+-----+--------+------------+------+--------+
| sid | sname  | sbirth     | ssex | sscore |
+-----+--------+------------+------+--------+
| 01  | 赵雷   | 1990-01-01 | 男   |     80 |
| 03  | 孙风   | 1990-05-20 | 男   |     80 |
+-----+--------+------------+------+--------+

解法2：（同上一题解法2）

select s.* ,ss.sscore
from student s, 
	 score ss, 
	 (select e.cid, max(e.sscore) max_score
	 from student s,score e,course c, teacher t
	 where s.sid=e.sid and e.cid=c.cid and c.tid=t.tid and t.tname='李四'
     group by e.cid) a   
where s.sid=ss.sid and a.cid=ss.cid and ss.sscore=a.max_score;

问题37 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
题意：查询那些存在两门课程成绩相等的学生

SELECT sc.sid,sc.cid,sc.sscore
FROM score sc
INNER JOIN score sc2 
ON sc.sid = sc2.sid AND sc.cid != sc2.cid AND sc.sscore =sc2.sscore
GROUP BY sc.sid, sc.cid;

select distinct s1.sid,s1.cid,s1.sscore
from score s1 
join score s2 
on s1.sscore=s2.sscore and s1.sid=s2.sid and s1.cid<> s2.cid;