50组关于sql的练习及思路解答

纯个人练习sql的过程。此文章延用了可到处搜到的50组sql，对于每句sql都会尽可能的做出多的解决方案并书写思路，希望可以帮助到更多人。中间有些题目没有明确的表示出要查询哪些项，这边就一切从简了（其实就是懒），大家对题目的模糊项，大可不必钻牛角尖。其实我写的时候想改来着，但是因为懒没有做修正。有任何不理解的地方欢迎留言，有更好的解法或者觉得解法有问题的也欢迎留言讨论。下面正题开始

首先是建表语句，同样的涉及到4张表student（学生信息表）， sc（学生成绩表）， course（课程信息表），teacher（教师信息表）

student表

CREATE TABLE student(Sid VARCHAR(10), Sname VARCHAR(10), Sage datetime, Ssex VARCHAR(10));
INSERT INTO student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO student VALUES('03' , '孙风' , '1990-05-20' , '男');
INSERT INTO student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO student VALUES('08' , '王菊' , '1990-01-20' , '女');

course表

CREATE TABLE course(Cid VARCHAR(10), Cname VARCHAR(10), Tid VARCHAR(10));
INSERT INTO course VALUES('01' , '语文' , '02');
INSERT INTO course VALUES('02' , '数学' , '01');
INSERT INTO course VALUES('03' , '英语' , '03');

teacher表

CREATE TABLE teacher(Tid VARCHAR(10), Tname VARCHAR(10));
INSERT INTO teacher VALUES('01' , '张三');
INSERT INTO teacher VALUES('02' , '李四');
INSERT INTO teacher VALUES('03' , '王五');

sc表

CREATE TABLE sc(Sid VARCHAR(10), Cid VARCHAR(10), score DECIMAL(18,1));
INSERT INTO sc VALUES('01' , '01' , 80);
INSERT INTO sc VALUES('01' , '02' , 90);
INSERT INTO sc VALUES('01' , '03' , 99);
INSERT INTO sc VALUES('02' , '01' , 70);
INSERT INTO sc VALUES('02' , '02' , 60);
INSERT INTO sc VALUES('02' , '03' , 80);
INSERT INTO sc VALUES('03' , '01' , 80);
INSERT INTO sc VALUES('03' , '02' , 80);
INSERT INTO sc VALUES('03' , '03' , 80);
INSERT INTO sc VALUES('04' , '01' , 50);
INSERT INTO sc VALUES('04' , '02' , 30);
INSERT INTO sc VALUES('04' , '03' , 20);
INSERT INTO sc VALUES('05' , '01' , 76);
INSERT INTO sc VALUES('05' , '02' , 87);
INSERT INTO sc VALUES('06' , '01' , 31);
INSERT INTO sc VALUES('06' , '03' , 34);
INSERT INTO sc VALUES('07' , '02' , 89);
insert INTO sc VALUES('07' , '03' , 98);

练习题目

1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
1.1查询同时存在" 01 "课程和" 02 "课程的情况
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
3.查询在 SC 表存在成绩的学生信息
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
4.1查有成绩的学生信息
5.查询「李」姓老师的数量
6.查询学过「张三」老师授课的同学的信息
7.查询没有学全所有课程的同学的信息
8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
10.查询没学过"张三"老师讲授的任一门课程的学生姓名
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
14.查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
15.1按各科成绩进行排序，并显示排名， Score 重复时合并名次
16.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
16.1. 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
18.查询各科成绩前三名的记录
19.查询每门课程被选修的学生数
20.查询出只选修两门课程的学生学号和姓名
21.查询男生、女生人数
22.查询名字中含有「风」字的学生信息
23.查询同名学生名单，并统计同名人数
24.查询 1990 年出生的学生名单
25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
30.查询存在不及格的课程
31.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
32.求每门课程的学生人数
33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
36.查询每门功成绩最好的前两名
37.统计每门课程的学生选修人数（超过 5 人的课程才统计）
38.检索至少选修两门课程的学生学号
39.查询选修了全部课程的学生信息
40.查询各学生的年龄，只按年份来算
41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
42.查询本周过生日的学生
43.查询下周过生日的学生
44.查询本月过生日的学生
45.查询下月过生日的学生

解题：

1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

思路一：查询学习了01课程的学生id存放至表A，学习了02课程的学生id存放至表B，通过保证id相同，且A的score>B的score两个条件完成查询结果。可以用多表联合查询，也可以用表连接查询（其实多表联合查询也是表连接查询，只是写法不同。这里两种都提供）。将查询的结果与student 通过id连接，输出最后需要查询的信息和分数

写法一：

SELECT student.*, C.Score01, C.Score02 FROM
(SELECT A.*, B.Score02 FROM
(SELECT Sid, (score)'Score01' FROM sc WHERE Cid = '01') AS A,
(SELECT Sid, (score)'Score02' FROM sc WHERE Cid = '02') AS B
WHERE A.Sid = B.Sid AND A.Score01 > B.Score02) AS C
LEFT JOIN student ON student.Sid = C.Sid

写法二：

SELECT student.*, C.Score01, C.Score02 FROM
(SELECT A.Sid, A.Score01, B.Score02 FROM
(SELECT Sid, (score)'Score01' FROM sc WHERE Cid = '01') AS A 
INNER JOIN (SELECT Sid, (score)'Score02' FROM sc WHERE Cid = '02') AS B 
ON A.Sid = B.Sid AND A.Score01 > B.Score02) AS C
LEFT JOIN student ON student.Sid = C.Sid

思路二：将sc表对Sid进行分组，分组后，利用CASE WHEN语句查询每个Sid的01和02的成绩，通过01的成绩大于02来确定最终的Sid，最后与student连接输出最终查询信息和分数

分组解法：

SELECT student.*, B.Score01, B.Score02 FROM
(SELECT * FROM
(SELECT Sid,
MAX(CASE Cid WHEN '01' THEN score ELSE NULL END)'Score01',
MAX(CASE Cid WHEN '02' THEN score ELSE NULL END)'Score02' FROM sc GROUP BY Sid) AS A 
WHERE A.Score01 > A.Score02) AS B LEFT JOIN student on B.Sid = student.Sid

数据：

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

思路：思路同1， 先查询出学习了01，02的学生id ，通过id相等筛选查询结果，同样两种写法

写法1

SELECT A.Sid FROM
(SELECT Sid FROM sc WHERE Cid = '01') AS A,
(SELECT Sid FROM sc WHERE Cid = '02') AS B
WHERE A.Sid = B.Sid

写法2

 SELECT A.Sid FROM
(SELECT Sid FROM sc WHERE Cid = '01') AS A INNER JOIN
(SELECT Sid FROM sc WHERE Cid = '02') AS B ON A.Sid = B.Sid

数据：

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

思路：这个因为需要显示不存在的情况，就只能用表连接了 。使用左连接和右连接都可以。这里只提供左连接的解法

解法：

SELECT * FROM
(SELECT Sid FROM sc WHERE Cid = '01') AS A LEFT JOIN 
(SELECT Sid FROM sc WHERE Cid = '02') AS B
ON A.Sid = B.Sid

数据：

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况

思路：先查询学习02的学生id，学习01的学生id，需要查找的为学习02的学生id不在01的学生id中的情况。此题也可以用表连接完成。

嵌套查询：

SELECT Sid FROM sc WHERE Cid = '02' AND Sid NOT IN
(SELECT Sid FROM sc WHERE Cid = '01')

表连接：

SELECT B.Sid FROM
(SELECT * FROM sc WHERE Cid = '01') AS A RIGHT JOIN 
(SELECT * FROM sc WHERE Cid = '02') AS B ON A.Sid = B.Sid 
WHERE A.Sid IS NULL

数据：

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

思路：对学生id分组用AVG函数计算平均分数，再表连接即可。

解法：

SELECT A.*, student.Sname FROM
(SELECT Sid, AVG(score)'avgScore' FROM sc
GROUP BY Sid
HAVING AVG(score) >= 60) AS A LEFT JOIN student
ON student.Sid = A.Sid

数据：

3.查询在 SC 表存在成绩的学生信息

思路：直接查询sc的学生编号即可，同样用表连接也可以做。统计学生编号可以用GROUP BY 也可以用DISTINCT

SELECT * FROM student WHERE Sid IN (SELECT Sid FROM sc GROUP BY Sid)
SELECT * FROM student WHERE Sid IN (SELECT DISTINCT Sid FROM sc)
SELECT DISTINCT student.* FROM student,sc WHERE student.Sid = sc.Sid

数据：

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

思路：这题只能表连接。对Sid GROUP BY之后在对Cid 和score 分别用COUNT和SUM函数，最后通过表连接完成查询

SELECT student.Sid, Sname, A.countCid, A.sumScore FROM student LEFT JOIN
(SELECT Sid, COUNT(Cid)'countCid', SUM(score)'sumScore' FROM sc 
GROUP BY Sid) AS A ON student.Sid = A.Sid

数据：

4.1查有成绩的学生信息

思路：这题其实和题3一样的，这边扩展下IN 和 EXISTS。当查询子表的数据非常大时，选用EXISTS要比IN高效很多。

在子查询表的数据比外部查询表的数据小时，用IN，反之用EXISTS。

关于EXISTS和IN 的区别和原理，可自行百度。

SELECT * FROM student WHERE Sid IN (SELECT Sid FROM sc)
SELECT * FROM student WHERE EXISTS(SELECT Sid FROM sc WHERE student.Sid = Sid)

5.查询「李」姓老师的数量

这题没啥好说的，注意下LIKE的用法即可

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

6.查询学过「张三」老师授课的同学的信息

思路：这题属于嵌套查询了，直接根据‘张三’ 找Tid 再找Cid 最后找到Sid 即可完成，也可以通过多表联合查询和表连接实现

嵌套查询：

SELECT * FROM student WHERE Sid IN
(SELECT Sid FROM sc WHERE Cid IN
(SELECT Cid FROM course WHERE Tid =
(SELECT Tid FROM teacher WHERE Tname = '张三')))

多表联合查询：

SELECT student.* FROM student, sc, course, teacher
WHERE student.Sid = sc.Sid AND sc.Cid = course.Cid 
AND course.Tid = teacher.Tid AND teacher.Tname = '张三'

表连接：

SELECT student.* FROM 
(SELECT sc.Sid FROM
(SELECT course.Cid FROM 
(SELECT Tid FROM teacher WHERE Tname = '张三') AS A 
LEFT JOIN course ON A.Tid = course.Tid) AS B 
LEFT JOIN sc ON sc.Cid = B.Cid) AS C 
LEFT JOIN student ON C.Sid = student.Sid

数据：

7.查询没有学全所有课程的同学的信息

思路：先查询课程的总数，然后根据Sid分组查看每个Sid学的Cid数量是否小于课程总数来查询出Sid，最后输出信息，此题也用表连接做。

SELECT * FROM student WHERE Sid IN
(SELECT Sid FROM sc GROUP BY Sid
HAVING COUNT(Cid) < (SELECT COUNT(DISTINCT Cid) FROM course))

数据:

如果说08学生也要再内的话，那么思路转变一下即可，先找出学满了课程的Sid，然后使用NOT IN 即可

SELECT * FROM student WHERE Sid NOT IN
(SELECT Sid FROM sc GROUP BY Sid
HAVING COUNT(Cid) = (SELECT COUNT(DISTINCT Cid) FROM course))

表连接同样可以

SELECT * FROM student WHERE Sid IN
(SELECT A.Sid FROM
(SELECT DISTINCT Sid FROM student) AS A LEFT JOIN sc ON A.Sid = sc.Sid 
GROUP BY A.Sid HAVING COUNT(Cid) < (SELECT COUNT(DISTINCT Cid) FROM course))

数据：

8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

思路：先查询01同学学习的课程id，再查询Cid在前面的查询结果中且不是01学生的Sid

SELECT * FROM student WHERE Sid IN
(SELECT DISTINCT Sid FROM sc WHERE Sid != '01' AND Cid IN
(SELECT DISTINCT Cid FROM sc WHERE Sid = '01'))

数据：

9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息

思路：同第8题，不同点在于，对于完全相同，则只需要查询，其他任意一名同学所学的所有Cid均在01学过的Cid中，并且其Cid总和与01的Cid总和相同，用两个条件可以确定完全相同。

SELECT * FROM student WHERE Sid IN
(SELECT Sid FROM sc WHERE Sid != '01' AND Cid IN 
(SELECT Cid FROM sc WHERE Sid = '01') GROUP BY Sid 
HAVING COUNT(Cid) = (SELECT COUNT(Cid) FROM sc WHERE Sid = '01'))

数据：

10.查询没学过"张三"老师讲授的任一门课程的学生姓名

思路：反向查询，先查询学过”张三“老师讲授的任一门课程的学生id，然后对结果集取反即可。同样使用表连接也可以做

SELECT Sname FROM student WHERE Sid NOT IN
(SELECT Sid FROM sc WHERE Cid IN
(SELECT Cid FROM course WHERE Tid =
(SELECT Tid FROM teacher WHERE Tname = '张三')))

数据：

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

思路：分组查询分数小于60且Cid数量小于2的Sid，在进行表关联，同样进行多次表连接也可以

SELECT A.*, Sname FROM student RIGHT JOIN
(SELECT Sid, AVG(score)'avgScore' FROM sc WHERE score < 60
GROUP BY Sid
HAVING COUNT(Cid) >= 2) AS A ON student.Sid = A.Sid

表连接：

SELECT student.Sid, student.Sname, A.avgScore FROM
(SELECT Sid, AVG(score)'avgScore' FROM sc WHERE score < 60 
GROUP BY Sid HAVING COUNT(score) >= 2) AS A LEFT JOIN student on A.Sid = student.Sid

数据：

12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

思路：先双表联合查询，最后设置排序方式

SELECT student.* FROM student, sc
WHERE Cid = '01' AND score < 60 AND student.Sid = sc.Sid
GROUP BY student.Sid
ORDER BY sc.score DESC

数据：

13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

思路：需要用到CASE WHEN 对于根据Sid分组后的Cid进行统计，以01学生为例，分组后，Cid包含01，02，03，通过CASE语句判断 若为01则取分数，否则为0，最后取最大值即为01的分数，02，03同理。

SELECT Sid, AVG(score)'avgScore',
MAX(CASE Cid WHEN '01' THEN score ELSE 0 END)'score01',
MAX(CASE Cid WHEN '02' THEN score ELSE 0 END)'score02',
MAX(CASE Cid WHEN '03' THEN score ELSE 0 END)'score03'
FROM sc GROUP BY Sid
ORDER BY AVG(score) DESC

数据：

14.查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

思路：通过CASE WHEN 算出各个分段的人数比例，然后使用CONVERT 将数据转换成两位小数，在根据条件排序即可。此题可以用联合查询，也可以用表连接，关于其中用到的CONVERT 和DECIMAL解释一下：

CONVERT 为转换函数，DECIMAL为格式函数，CONVERT的参数为：expr， type 前者为表达式，后者为希望转换成的参数类型。DECIMAL的参数为：N，M，N表示为这个数据一共有多少位。M表示这个数据有多少位小数，则整数位为N-M，sql中DECIMAL(5, 2)，表示数据5位，小数为2位，整数为3位。

表连接：

SELECT course.Cname, A.* FROM
((SELECT Cid, MAX(score), MIN(score), AVG(score), COUNT(Sid)'peopleCount',
CONVERT(SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END)*1.00/COUNT(score)*100, DECIMAL(5, 2))'passRate',
CONVERT(SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END)*1.00/COUNT(score)*100, DECIMAL(5, 2))'middleRate',
CONVERT(SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END)*1.00/COUNT(score)*100, DECIMAL(5, 2))'goodRate',
CONVERT(SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END)*1.00/COUNT(score)*100, DECIMAL(5, 2))'excellentRate'
FROM sc GROUP BY Cid) AS A LEFT JOIN course ON course.Cid = A.Cid)
ORDER BY A.peopleCount DESC, A.Cid

联合查询：

SELECT sc.Cid, Cname, MAX(score), MIN(score), AVG(score), COUNT(Sid)'peopleCount',
CONVERT(SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END)*1.00/COUNT(score)*100, DECIMAL(5, 2))'passRate',
CONVERT(SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END)*1.00/COUNT(score)*100, DECIMAL(5, 2))'middleRate',
CONVERT(SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END)*1.00/COUNT(score)*100, DECIMAL(5, 2))'goodRate',
CONVERT(SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END)*1.00/COUNT(score)*100, DECIMAL(5, 2))'excellentRate'
FROM sc, course WHERE sc.Cid = course.Cid
GROUP BY sc.Cid
ORDER BY peopleCount DESC, sc.Cid

数据：

15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

思路：使用RANK() OVER()即可，排名的这几个查询结果就不放了。有点大

SELECT *, RANK() OVER(ORDER BY score DESC) FROM sc

15.1按各科成绩进行排序，并显示排名， Score 重复时合并名次

思路：使用DENSE_RANK() OVER即可

SELECT *, DENSE_RANK() OVER(ORDER BY score DESC) FROM sc

16.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

思路：同15

SELECT Sid, SUM(score), RANK() OVER(ORDER BY SUM(score) DESC) FROM sc GROUP BY Sid

16.1. 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

同15.1

17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

思路同14，比14还简单。联合查询和表连接均可。

联合查询：

SELECT sc.Cid, course.Cname,
SUM(CASE WHEN score >= 0 AND score < 60 THEN 1 ELSE 0 END)'0-60Count',
(CONVERT(SUM(CASE WHEN score >= 0 AND score < 60 THEN 1 ELSE 0 END)*1.00/COUNT(score)*100, DECIMAL(5, 2)))'0-60Rate',
SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END)'60-70Count',
(CONVERT(SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END)*1.00/COUNT(score)*100, DECIMAL(5, 2)))'60-70Rate',
SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END)'70-85Count',
(CONVERT(SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END)*1.00/COUNT(score)*100, DECIMAL(5, 2)))'70-85Rate',
SUM(CASE WHEN score >= 85 AND score <= 100 THEN 1 ELSE 0 END)'85-100Count',
(CONVERT(SUM(CASE WHEN score >= 85 AND score <= 100 THEN 1 ELSE 0 END)*1.00/COUNT(score)*100, DECIMAL(5, 2)))'85-100Rate'
FROM sc, course WHERE sc.Cid = course.Cid 
GROUP BY sc.Cid

表连接：

SELECT course.Cname, A.* FROM
(SELECT Cid, 
SUM(CASE WHEN score >= 0 AND score < 60 THEN 1 ELSE 0 END)'0-60Count',
CONVERT(SUM(CASE WHEN score >= 0 AND score < 60 THEN 1 ELSE 0 END)*1.00/COUNT(*)*100, DECIMAL(5,2))'0-60Rate',
SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END)'60-70Count',
CONVERT(SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END)*1.00/COUNT(*)*100, DECIMAL(5,2))'60-70Rate',
SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END)'70-85Count',
CONVERT(SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END)*1.00/COUNT(*)*100, DECIMAL(5,2))'70-85Rate',
SUM(CASE WHEN score >= 85 AND score <= 100 THEN 1 ELSE 0 END)'85-100Count',
CONVERT(SUM(CASE WHEN score >= 85 AND score <= 100 THEN 1 ELSE 0 END)*1.00/COUNT(*)*100, DECIMAL(5,2))'85-100Rate'
FROM sc GROUP BY Cid) AS A LEFT JOIN course ON A.Cid = course.Cid

数据：

18 查询各科成绩前三名的记录

这题比较难搞，也是想了好久才想明白的。

思路一：通用解法，对score进行降序排序，然后对Cid进行分组，寻找排名小于等于3则可认为是前三名了

PARTITION BY是分析函数，通过某个字段进行分组，可以保留分组后的全部数据，而GROUP BY会对分组的结果进行聚合。可以对SELECT *, RANK() OVER(PARTITION BY Cid ORDER BY score DESC)'Rank' FROM sc 语句 将PARTITION BY 替换成GROUP BY，通过查询结果理解两个的不同。

SELECT * FROM
(SELECT *, RANK() OVER(PARTITION BY Cid ORDER BY score DESC)'Rank' FROM sc)AS A
WHERE A.Rank <= 3

思路二：暴力解法，寻找和自己学习的课程id相同，但是分数比自己高的人的总数，如果总数小于3，则证明自己是前三，这边一定是小于三，因为包括了自己在内。

SELECT * FROM sc AS A WHERE
(SELECT COUNT(*) FROM sc WHERE A.Cid = Cid AND A.score < score) < 3
ORDER BY A.Cid, A.score DESC

思路三：将表sc自连接，道理同思路二。先将表sc自连接，where条件同思路二，这样则能找出，对于同一个课程，分数比自己高的数据有哪些。如果发现分数比自己高的只有2个及以下，则证明了自己是这个课程的前三。下面的sql先求出，对于同一个课程，分数比自己高的数据有哪些。全部数据可以自行执行查看，这里只列一小部分

SELECT * FROM sc AS A LEFT JOIN sc ON A.Cid = sc.Cid AND A.score < sc.score
ORDER BY A.Cid, A.score DESC

由上图数据可以看出，对于01课程，没有比学号03和01分数更高的了，则03和01肯定是前三的数据，05同理，但是对于学号为02的学生来说，有3个同学比他成绩高，则他必然不是前三了。04，06同学同理。所以我们只需要统计相同Cid下，Sid出现的个数即可，如果Sid出现2次以下，那么可以证明这个学生在这门课程是前三，反之不是前三，想要统计Sid，其实只需要对Sid和Cid进行GROUP BY然后COUNT一下就可以解决了（因为我们在讨论Sid出现次数的时候，都是在Cid固定的前提下讨论的，只有明确Sid和Cid才能确定一条数据。如果只考虑对Sid分组，恰好某个Sid在两门课程中都是前三，就会被合并掉的）。最终sql如下：

SELECT A.* FROM sc AS A LEFT JOIN sc ON A.Cid = sc.Cid AND A.score < sc.score
GROUP BY A.Sid, A.Cid
HAVING COUNT(A.Sid) < 3
ORDER BY A.Cid, A.score DESC

数据：

19.查询每门课程被选修的学生数

这题直接对Cid分组，在COUNT(Sid)即可

SELECT Cid, COUNT(Sid) FROM sc GROUP BY Cid

数据：

20.查询出只选修两门课程的学生学号和姓名

此题使用联合查询，嵌套查询，表连接均可

联合查询：

SELECT student.Sid, student.Sname FROM sc, student
WHERE sc.Sid = student.Sid
GROUP BY sc.Sid
HAVING COUNT(Cid) = 2

嵌套查询：

SELECT Sid, Sname FROM student WHERE Sid IN
(SELECT Sid FROM sc GROUP BY Sid HAVING COUNT(Cid) = 2)

表连接：

SELECT student.Sid, student.Sname FROM
(SELECT Sid FROM sc GROUP BY Sid HAVING COUNT(Cid) = 2) AS A
LEFT JOIN student ON student.Sid = A.Sid

数据：

21.查询男生、女生人数

这题直接对Ssex分组，在COUNT(*)即可

SELECT Ssex, COUNT(*) FROM student GROUP BY Ssex

数据：

22.查询名字中含有「风」字的学生信息

此题了解LIKE '%str%'，'%str'，'str%' 的用法即可，和题目5类似

SELECT * FROM student WHERE Sname LIKE '%风%'

 数据：

23.查询同名学生名单，并统计同名人数

此题对Sname GROUP BY即可，同样嵌套查询，表自连接均可

常规解法：

SELECT Sname, COUNT(Sid) FROM student 
GROUP BY Sname
HAVING COUNT(Sid) > 1

嵌套查询：

SELECT Sname, COUNT(Sid) FROM student AS A WHERE Sid IN
(SELECT Sid FROM student WHERE A.Sname = Sname AND A.Sid != Sid)


SELECT Sname, COUNT(Sid) FROM student WHERE Sname IN
(SELECT Sname FROM student
GROUP BY Sname HAVING COUNT(Sid) > 1)

数据：

24.查询 1990 年出生的学生名单

思路：考察YEAR关键字

SELECT * FROM student WHERE YEAR(Sage) = 1990

数据：

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

SELECT Cid, AVG(score) FROM sc
GROUP BY Cid
ORDER BY AVG(score) DESC, Cid

数据：

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

思路：先将Sid分组，在查询平均成绩即可，使用联合查询和表连接均可

联合查询：

SELECT sc.Sid, Sname, AVG(score) FROM sc, student
WHERE sc.Sid = student.Sid
GROUP BY Sid
HAVING AVG(score) >= 85

表连接：

SELECT A.Sid, A.Sname, B.avgScore FROM Student A LEFT JOIN
(SELECT Sid, AVG(score)'avgScore' FROM sc 
GROUP BY Sid)B on A.Sid = B.Sid
where B.avgScore > 85

数据：

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

联合查询：

SELECT Sname, score FROM student, sc, course WHERE
sc.Cid = course.Cid AND course.Cname = '数学' AND sc.Sid = student.Sid
GROUP BY sc.Sid
HAVING sc.score < 60

表连接：

SELECT B.Sname, A.score FROM
(SELECT * FROM sc WHERE score < 60 AND Cid =
(SELECT Cid FROM course WHERE Cname='数学')) AS A
LEFT JOIN Student AS B on A.Sid = B.Sid

数据：

28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

这题涉及到了没成绩，没选课的情况，所以只能表连接了

SELECT student.Sid, Sname, Cid, score FROM student
LEFT JOIN sc ON student.Sid = sc.Sid

数据有些多就不罗列了

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

思路：根据Sid和Cid将student，course，sc三张表关联起来，联合查询即可，同样表连接也可以

联合查询

SELECT Sname, Cname, score FROM student, course, sc
WHERE score > 70 AND sc.Sid = student.Sid AND sc.Cid = course.Cid

表连接

SELECT A.Sname, D.Cname, D.score FROM
(SELECT B.*, C.Cname FROM (SELECT * FROM SC WHERE score > 70) AS B 
LEFT JOIN Course C ON B.Cid = C.Cid) AS D 
LEFT JOIN Student AS A ON D.Sid = A.Sid

数据：

30.查询存在不及格的课程

这题使用GROUP BY 或者DISTINCT对Cid去重均可

SELECT DISTINCT Cid FROM sc WHERE score < 60

SELECT Cid FROM sc WHERE score < 60 GROUP BY Cid

数据：

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

此题先查询课程是01且分数在80以上的Sid，再使用联合查询和表连接查询Sname即可

联合查询：

SELECT sc.Sid, Sname FROM sc, student WHERE
sc.Sid = student.Sid AND Cid = '01' AND score >= 80

表连接：

SELECT A.Sid, Sname FROM student RIGHT JOIN
(SELECT Sid FROM sc WHERE Cid = '01' AND score >= 80) AS A 
ON A.Sid = student.Sid

数据：

32.求每门课程的学生人数

对Cid进行分组再COUNT

SELECT Cid, COUNT(Sid) FROM sc GROUP BY Cid

数据：

33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

先找出张三对应的Cid然后对成绩进行排序，取LIMIT 或者HAVING MAX 均可，这里推荐LIMIT，使用联合查询，嵌套查询，表连接均可，这边只列出联合查询和嵌套查询

联合查询：

SELECT student.*, sc.score, sc.cid FROM student, teacher, course, sc 
WHERE teacher.tid = course.tid
AND sc.sid = student.sid
AND sc.cid = course.cid
AND teacher.tname = "张三"
ORDER BY score DESC
LIMIT 1

SELECT student.*, sc.score, sc.cid FROM student, teacher, course, sc 
WHERE teacher.tid = course.tid
AND sc.sid = student.sid
AND sc.cid = course.cid
AND teacher.tname = "张三"
HAVING MAX(sc.score);

嵌套查询：

SELECT student.*, Cid, score FROM student, sc WHERE
sc.Sid = student.Sid AND Cid =
(SELECT Cid FROM course WHERE Tid =
(SELECT Tid FROM teacher WHERE Tname = '张三'))
ORDER BY score DESC LIMIT 1

数据：

34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

为了验证此题，先把数据改一下，这样最高分就有两个了

UPDATE sc SET score=90 WHERE Sid = "07" AND Cid ="02"

思路一：找出张三老师对应的课程及成绩，对成绩进行排名，找出排名为1的学生即可，此题表连接也可以做的就不再重述了

SELECT student.*, score FROM student,
(SELECT Sid, score, DENSE_RANK() OVER(ORDER BY score DESC)'rank' FROM sc WHERE Cid =
(SELECT Cid FROM course WHERE Tid =
(SELECT Tid FROM teacher WHERE Tname = '张三'))) AS A
WHERE student.Sid = A.Sid AND A.rank = 1

思路二：沿用上一题的思路，找出最大的分数，然后再找和这个分数相同的

SELECT student.*, sc.score, sc.Cid FROM student, teacher, course, sc 
WHERE teacher.tid = course.tid
AND sc.Sid = student.Sid
AND sc.Cid = course.Cid
AND teacher.tname = "张三"
AND sc.score = (
    SELECT MAX(sc.score) 
    FROM sc,student, teacher, course
    WHERE teacher.tid = course.tid
    AND sc.Sid = student.Sid
    AND sc.Cid = course.Cid
    AND teacher.tname = "张三"
)

数据：

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

此题用联合查询和表自连接都可以完成，查找Cid不同但是Sid和score相同的即可。但是要注意的是查找出来的会有重复的数据，最后对Cid分组即可。也可以自行查看重复数据，然后对数据进行操作。

SELECT A.* FROM sc AS A, sc WHERE
A.Sid = sc.Sid AND A.Cid != sc.Cid AND A.score = sc.score
GROUP BY A.Cid

数据：

36.查询每门功成绩最好的前两名

这题上面18有写，不重复写了。

37.统计每门课程的学生选修人数（超过 5 人的课程才统计）

对Cid分组，使用HAVING筛选数据即可

SELECT Cid, COUNT(Sid) FROM sc
GROUP BY Cid
HAVING COUNT(Sid) > 5

数据：

38.检索至少选修两门课程的学生学号

对Sid分组，使用HAVING筛选数据即可

SELECT Sid FROM sc
GROUP BY Sid
HAVING COUNT(Cid) >= 2

数据：

39.查询选修了全部课程的学生信息

对Sid分组，对分组后Cid取COUNT结果，和对course表取COUNT的结果对比，相同则为选修了全部的课程

SELECT student.* FROM student, sc
WHERE sc.Sid = student.Sid
GROUP BY sc.Sid
HAVING COUNT(Cid) = (SELECT DISTINCT COUNT(*)'countCid' FROM course)

数据：

40.查询各学生的年龄，只按年份来算

考察YEAR和CURDATE的用法

SELECT *, (YEAR(CURDATE()) - YEAR(Sage))'age' FROM student

数据：

41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

考察关键字TIMESTAMPDIFF的使用，一般都用此种方式计算年龄，TIMESTAMPDIFF为计算真实天数，日期小的放前面，日期大的放后面，第一个参数为转换出的格式，年月日时分秒等。

SELECT *, TIMESTAMPDIFF(YEAR, Sage, CURDATE())'age' FROM student

数据：

42.查询本周过生日的学生

考察关键字WEEKOFYEAR的用法。WEEKOFYEAR参数为时间戳，返回值为当前为第几周

SELECT * FROM student WHERE WEEKOFYEAR(Sage) = WEEKOFYEAR(CURTIME())

这个要根据实际情况查看数据，所以这边不列数据

43.查询下周过生日的学生

同上题，数据+1即可

SELECT * FROM student WHERE WEEKOFYEAR(Sage) = WEEKOFYEAR(CURTIME()) + 1

44.查询本月过生日的学生

考察关键字MONTH的用法，参数同WEEKOFYEAR

SELECT * FROM student WHERE MONTH(Sage) = MONTH(CURTIME())

45.查询下月过生日的学生

SELECT * FROM student WHERE MONTH(Sage) = MONTH(CURTIME()) + 1