MySQL经典50题：面试必备

原创

皮大大

修改于 2021-09-03 10:43:52

1.3K0

修改于 2021-09-03 10:43:52

标题	MySQL经典50题解析及答案
作者	Peter
微信	756803877
公众号	尤而小屋
时间	2021-09-02

MySQL经典50题解析及答案

下面是网传经典的MySQL50题的习题及参考答案?，供参考和学习，有更好的方法或者不恰当的地方，欢迎提出来

题目1

题目要求

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

SQL实现

 -- 方法1
 select
   a.*
   ,b.s_score as 1_score
   ,c.s_score as 2_score
 from Student a
 left join Score b on a.s_id = b.s_id  and b.c_id = '01'   -- 两个表通过学号连接，指定01
 left join Score c on a.s_id = c.s_id and c.c_id='02' or c.c_id is NULL -- 指定02，或者c中的c_id直接不存在
 -- 为NULL的条件可以不存在，因为左连接中会直接排除c表中不存在的数据，包含NULL
 where b.s_score > c.s_score;   -- 判断条件
 ?
 ?
 -- 方法2：直接使用where语句
 select
   a.*
   ,b.s_score as 1_score
   ,c.s_score as 2_score
 from Student a, Score b, Score c
 where a.s_id=b.s_id   -- 列出全部的条件
 and a.s_id=c.s_id
 and b.c_id='01'
 and c.c_id='02'
 and b.s_score > c.s_score;   -- 前者成绩高

 -- 方法1：通过连接方式实现
 select
   a.*
   ,b.s_score as 1_score
   ,c.s_score as 2_score
 from Student a
 left join Score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL   -- 包含NULL的数据
 join score c on a.s_id=c.s_id and c.c_id='02'
 where b.s_score < c.s_score;
 ?
 -- 通过where子句实现
 select
   a.*
   ,b.s_score as 1_score
   ,c.s_score as 2_score
 from Student a, Score b, Score c
 where a.s_id=b.s_id
 and a.s_id=c.s_id
 and b.c_id='01'
 and c.c_id='02'
 and b.s_score < c.s_score;   -- 前者比较小

 -- 执行顺序：先执行分组，再执行avg平均操作
 select
   b.s_id
   ,b.s_name
   ,round(avg(a.s_score), 2) as avg_score
 from Student b
 join Score a
 on b.s_id = a.s_id
 group by b.s_id   -- 分组之后查询每个人的平均成绩
 having avg_score >= 60;
 ?
 -- 附加题：总分超过200分的同学
 select
   b.s_id
   ,b.s_name
   ,round(sum(a.s_score),2) as sum_score    -- sum求和
 from Student b
 join Score a
 on b.s_id=a.s_id
 group by b.s_id
 having sum_score > 200;

 select
   b.s_id
   ,b.s_name
   ,round(avg(a.s_score), 2) as avg_score   -- round四舍五入函数
 from Student b
 join Score a
 on b.s_id = a.s_id
 group by b.s_id   -- 分组之后查询每个人的平均成绩
 having avg_score < 60;

 select
   a.s_id
   ,a.s_name
   ,0 as avg_score
 from Student a
 where a.s_id not in (  -- 学生的学号不在给给定表的学号中
   select distinct s_id -- 查询出全部的学号
   from Score
 );

 select
   S.s_id
   ,S.s_name
   ,round(avg(ifnull(C.s_score,0)), 2) as avg_score   -- ifnull 函数：第一个参数存在则取它本身，不存在取第二个值0
 from Student S
 left join Score C
 on S.s_id = C.s_id
 group by s_id
 having avg_score < 60;

 select
   a.s_id
   ,a.s_name
   ,ROUND(AVG(b.s_score), 2) as avg_score
 from Student a
 left join Score b on a.s_id = b.s_id
 GROUP BY a.s_id
 HAVING avg_score < 60 or avg_score is null;   -- 最后的NULL判断

 select
   a.s_id
   ,a.s_name
   ,count(b.c_id) as course_number   -- 课程个数
   ,sum(b.s_score) as scores_sum  -- 成绩总和
 from Student a
 left join Score b
 on a.s_id = b.s_id
 group by a.s_id,a.s_name;

先从Score表中看看哪些人是满足要求的：01-05同学是满足的

通过自连接查询的语句如下：

查询出学号后再匹配出学生信息：

通过where语句实现：

方法3的实现：

题目10 题目需求查询学过01课程，但是没有学过02课程的学生信息（注意和上面?题目的区别） SQL实现首先看看哪些同学是满足要求的：只有06号同学是满足的

错误思路1 直接将上面一题的结果全部排出，导致那些没有学过01课程的学生也出现了：07，08 select s1.* from Student s1 where s_id not in ( -- 直接将上面一题的结果全部排出，导致那些没有学过01课程的学生也出现了：07，08 select s2.s_id from Score s2 join Score s3 on s2.s_id=s3.s_id where s2.c_id='01' and s3.c_id ='02' );

错误思路2 将上面题目中的02课程直接取反，导致同时修过01，02，03或者只修01，03的同学也会出现 select s1.* from Student s1 where s_id in ( select s2.s_id from Score s2 join Score s3 on s2.s_id=s3.s_id where s2.c_id='01' and s3.c_id !='02' -- 直接取反是不行的，因为修改(01,02,03)的同学也会出现 );

正确思路 https://www.jianshu.com/p/9abffdd334fa -- 方法1：根据两种修课情况来判断 select s1.* from Student s1 where s1.s_id in (select s_id from Score where c_id='01') -- 修过01课程，要保留 and s1.s_id not in (select s_id from Score where c_id='02'); -- 哪些人修过02，需要排除

！！！！！方法2：先把06号学生找出来 select * from Student where s_id in ( select s_id from Score where c_id='01' -- 修过01课程的学号 and s_id not in (select s_id -- 同时学号不能在修过02课程中出现 from Score where c_id='02') );

题目11 题目需求查询没有学完全部课程的同学的信息 SQL实现 -- 自己的方法 select * -- 排除学号后得到的结果 from Student where s_id not in (select s_id from (select s_id, count(s_id) as number -- 3.最大课程数所在的学号需要排除 from Score group by s_id) s -- 取别名 where number=(select max(number) -- 2.保证最大的课程数 from( select s_id, count(s_id) as number -- 1.学号和个数统计（即修了几门课） from Score group by s_id)t)); -- 别名

自己的方法一开始在课程的最大数中没有使用Course表，导致多使用了一个临时表的结果，现在改成使用Course表的统计值（3）作为课程的总数： select s.* from Student s where s_id not in ( select s_id from Score s1 group by s_id having count(*) = (select count(*) from Course) );

-- 方法2：having select s.* from Student s -- 学生表 left join Score s1 -- 成绩表 on s1.s_id = s.s_id group by s.s_id -- 学号分组 having count(s1.c_id) < ( -- 分组后学生的课程数<3 select count(*) from Course -- 全部课程数=3 ) 题目12 题目需求查询至少有一门课与学号为01的同学所学相同的同学的信息 SQL实现首先看看结果的：因为01号同学修了全部课程，所以其他的同学都是满足要求，除了08号同学没有任何成绩，不符合。

具体实现过程为： select * -- 3、求出学生信息 from Student where s_id in ( select distinct s_id -- 2、找出满足课程在01学生课程中的全部学号（学生），学号去重，同时将01自己排除 from Score where c_id in ( select c_id from Score where s_id=01 -- 1、找出学号01同学的全部课程 ) and s_id != 01);

-- 方法2 select s1.* from Student s1 join Score s2 on s1.s_id = s2.s_id -- 学生表和成绩表的关联 and c_id in (select c_id from Score where s_id=01) -- 对课程进行限制，只在01学生的课程内 group by s1.s_id; -- 根据学号分组

题目13 题目需求查询和01同学学习的课程完全相同的同学的信息 SQL实现

自己的方法

select * from Student where s_id in (select s_id -- 3、步骤2中得到的学号是满足要求的 from(select distinct(s_id), count(c_id) number from Score group by s_id)t1 -- 1、学号和所修课程分组的结果t1 where number=3 -- 2、投机：选择出所修课程数是3（01修了3门）的学号 and s_id !=01 -- 01 本身排除 );

我们在上面的步骤2中不考虑直接指定3（where number=3），而是用01学生所修的课程数（虽然也是3）来代替： select * from Student where s_id in( select s_id -- 3、步骤2中得到的学号是满足要求的 from(select distinct(s_id), count(c_id) number from Score group by s_id)t1 -- 1、学号和所修课程分组的结果t1 where number=(select count(c_id) number from Score group by s_id having s_id=01) -- 2、改变的地方：使用学号01的课程数3来代替 and s_id !=01 -- 01 本身排除 );

需要进行排序的原因是防止出现这种情况：01修的课程顺序是：01，02，03；如果有同学修课的顺序是02，03，01，虽然顺序不同，但是本质上他们修的课程是相同的 使用排序后都会变成：01，02，03，保证结果相同 那么之后，我们只需要判断合并后和01号同学相同的结果即可，取出学号： select * -- 3、查询信息 from Student where s_id in( select s_id from Score group by s_id having group_concat(c_id order by c_id)=( -- 2、找出和01号学生分组合并结果相同的学号s_id；也需要排序 select group_concat(c_id order by c_id) -- 1、找出01号学生分组合并的结果，同时排序；排序很重要 from Score group by s_id having s_id=01) and s_id != 01 -- 将自身排除 );

题目14 题目需求查询没有修过张三老师讲授的任何一门课程的学生姓名 SQL实现自己的方法，具体过程如下： select s_name -- 4、学号取反找到学生姓名 from Student where s_id not in( select distinct(s_id) -- 3、课程号找到对应的学号 from Score where c_id=( select c_id -- 2、教师编号找到对应的课程号 from Course where t_id=( select t_id -- 1、姓名找到教师编号 from Teacher where t_name='张三') ));

-- 修过张老师课程的学生的学号 select distinct(s_id) from Score where c_id=( select c_id from Course where t_id=( select t_id from Teacher where t_name='张三')); -- 修过张三老师课的学生

题目15 题目需求查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 SQL实现首先看看哪些同学是满足两门或者两门以上是及格的

-- 2门及以上不及格的 select s_id ,round(avg(s_score)) avg_score from Score where s_score < 60 -- 小于60分，不及格 group by s_id having count(s_score) >= 2; -- 不及格的有2门以上 说明04，06是我们最终想要的结果

-- 自己的方法 select s.s_id ,s.s_name ,t.avg_score from Student s join (select s_id ,round(avg(s_score)) avg_score from Score where s_score < 60 group by s_id having count(s_score) >= 2)t on s.s_id=t.s_id

-- 参考方法1 select a.s_id, a.s_name, ROUND(AVG(b.s_score)) from Student a left join Score b on a.s_id = b.s_id where a.s_id in( select s_id from Score where s_score<60 group by s_id having count(1)>=2) group by a.s_id,a.s_name -- 参考方法2 select s.s_id ,s_name ,round(avg(s_score), 2) avg_score from Student s join Score sc on s.s_id=sc.s_id and sc.s_score < 60 -- 不及格 group by s.s_id -- 学号分组 having count(sc.c_id )>= 2; -- 2门课改进点上面的两种方法都没有考虑都08学生，3门都没有成绩，这个本题需要改进的地方。 题目16 题目需求检索01课程分数小于60，按分数降序排列的学生信息 SQL实现自己的方法如下：首先从Score表中找出哪些学生是满足这个要求： select s_id ,s_score from Score where s_score < 60 and c_id = 01;

然后直接将上面的结果和Student表查询： select s.* from Student s where s.s_id in ( select s_id ,s_score from Score where s_score < 60 and c_id = 01 );

select s.* ,t.s_score from Student s join (select s_id,s_score -- 2、Student和t的连接查询 from Score where s_score < 60 and c_id = 01 )t -- 1、将第一步结果作为中间表t on s.s_id=t.s_id;

-- 自己的方法2：两个表的直接连接查询+where条件 select s.* ,sc.s_score from Student s join Score sc on s.s_id=sc.s_id where sc.c_id=01 and sc.s_score < 60 order by sc.s_score desc; -- 默认就是降序desc

题目17 题目需求按平均成绩从高到低（降序）显示所有学生的所有课程的成绩以及平均成绩 SQL实现下面是自己的解法： 1、先求出每个同学的平均分，并降序排列 select s_id ,round(avg(s_score),2) avg_score from Score group by s_id order by 2 desc;

-- 自己的方法 select s.s_id ,s.c_id ,s.s_score ,t.avg_score from Score s join (select s_id ,round(avg(s_score),2) avg_score from Score group by s_id)t on s.s_id = t.s_id order by 4 desc; -- 指的是第4个字段

-- 参考方法1 select s.s_id ,(select s_score from Score where s_id=s.s_id and c_id='01') as 语文 ,(select s_score from Score where s_id=s.s_id and c_id='02') as 数学 ,(select s_score from Score where s_id=s.s_id and c_id='03') as 英语 ,round(avg(s_score),2) 平均分 from Score s group by s.s_id order by 5 desc;

select s.s_id ,max(case s.c_id when '01' then s.s_score end) 语文 ,max(case s.c_id when '02' then s.s_score end) 数学 ,max(case s.c_id when '03' then s.s_score end) 英语 ,avg(s.s_score) ,b.s_name -- 没有出现在group by子句中，导致报错 join Student b on s.s_id = b.s_id group by s.s_id order by 5 desc; 严格模式的报错： ERROR 1055 (42000): Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.b.s_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。 -- 参考方法2：将上面的b.s_name去掉 select s.s_id ,max(case s.c_id when '01' then s.s_score end) 语文 ,max(case s.c_id when '02' then s.s_score end) 数学 ,max(case s.c_id when '03' then s.s_score end) 英语 ,round(avg(s.s_score),2) avg_score from Score s join Student b on s.s_id = b.s_id group by s.s_id order by 5 desc;

题目18 题目需求查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率；及格：>=60，中等为：70-80，优良为：80-90，优秀为：>=90 SQL实现 思路清晰：统计每个阶段的总人数，再除以总共的人数即可 将成绩表和课程表联合起来进行查询：

case 语句用于对每个分数贴标签
sum 语句对相应的语句中的1进行求和

select s.c_id ,c.c_name ,max(s.s_score) ,min(s.s_score) ,round(avg(s.s_score), 2) ,round(100 * (sum(case when s.s_score >= 60 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 及格率 ,round(100 * (sum(case when s.s_score >= 70 and s.s_score <= 80 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 中等率 ,round(100 * (sum(case when s.s_score >= 80 and s.s_score <= 90 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 优良率 ,round(100 * (sum(case when s.s_score >= 90 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 优秀率 from Score s left join Course c on s.c_id = c.c_id group by s.c_id, c.c_name;

题目19 题目需求按照各科成绩进行排序，并且显示排名分析过程题目的意思是：将每科的成绩单独进行排名，类似如下的效果： 课程名分数排名英语991英语922英语893数学881数学852……………...SQL实现第一步：我们对Score表中的一门课程进行排名，比如01课程 select * from( select t1.c_id -- 课程号 ,t1.s_score -- 分数 ,(select count(distinct t2.s_score) -- 课程去重 from Score t2 where t2.s_score >= t1.s_score -- SQL实现排序 and t2.c_id = '01') rank from Score t1 -- 通过相同的表实现自连接 where t1.c_id = '01' order by t1.s_score desc )t1 上面是针对01课程，结果为：

第二步：我们将01、02、03课程全部连接起来，通过union实现

表的自连接
SQL实现排序

-- 自己的方法 select * from( select t1.c_id -- 课程号 ,t1.s_score -- 分数 ,(select count(distinct t2.s_score) -- 课程去重 from Score t2 where t2.s_score >= t1.s_score -- SQL实现排序 and t2.c_id = '01') rank from Score t1 -- 通过相同的表实现自连接 where t1.c_id = '01' order by t1.s_score desc )t1 union select * from( select t1.c_id -- 课程号 ,t1.s_score -- 分数 ,(select count(distinct t2.s_score) -- 课程去重 from Score t2 where t2.s_score >= t1.s_score -- SQL实现排序 and t2.c_id = '02') rank from Score t1 -- 通过相同的表实现自连接 where t1.c_id = '02' order by t1.s_score desc )t2 union select * from( select t1.c_id -- 课程号 ,t1.s_score -- 分数 ,(select count(distinct t2.s_score) from Score t2 where t2.s_score >= t1.s_score and t2.c_id = '03') rank from Score t1 where t1.c_id = '03' order by t1.s_score desc )t3;

-- 参考代码 select * from (select t1.c_id, t1.s_score, (select count(distinct t2.s_score) from Score t2 where t2.s_score>=t1.s_score and t2.c_id='01') rank from Score t1 where t1.c_id='01' order by t1.s_score desc) t1 union select * from (select t1.c_id ,t1.s_score ,(select count(distinct t2.s_score) from Score t2 where t2.s_score>=t1.s_score and t2.c_id='02') rank from Score t1 where t1.c_id='02' order by t1.s_score desc) t2 union select * from (select t1.c_id, t1.s_score, (select count(distinct t2.s_score) from Score t2 where t2.s_score>=t1.s_score and t2.c_id='03') rank from Score t1 where t1.c_id='03' order by t1.s_score desc) t3 题目20 题目需求查询学生的总成绩，并进行排名 SQL实现 1、先查询每个学生的总成绩 select s_id ,sum(s_score) from Score group by s_id order by 2 desc;

将上面的结果和学生信息表进行关联查询： -- select s.s_name ,s.s_id ,t.score from Student s join(select s_id ,sum(s_score) score from Score group by s_id order by 2 desc )t on s.s_id = t.s_id;

-- 不使用中间表查询 select s.s_id ,s.s_name ,sum(sc.s_score) score from Student s join Score sc on s.s_id = sc.s_id group by s.s_id order by 3 desc;

如果想给排名加上一个排序号，参考之前的文章 -- 加上排序号 select t1.s_id ,t1.s_name, t1.score ,(select count(t2.score) from(select s.s_id, s.s_name, sum(sc.s_score) score from Student s join Score sc on s.s_id = sc.s_id group by s.s_id order by 3 desc)t2 -- t2和t1相同 where t2.score > t1.score) + 1 as rank from( select s.s_id ,s.s_name ,sum(sc.s_score) score from Student s join Score sc on s.s_id = sc.s_id group by s.s_id order by 3 desc)t1 -- t1 order by 3 desc;

题目21 题目需求查询不同老师所教不同课程平均分从高到低显示 SQL实现先找出每个老师教授了哪些课程： select c.c_name ,t.t_name from Course c left join Teacher t on c.t_id = t.t_id;

将上面的结果和成绩表连接起来： select c.c_name ,t.t_name ,round(avg(s.s_score),2) score -- 课程分组后再求均值 from Course c -- 主表，通过两次连接 left join Teacher t on c.t_id = t.t_id left join Score s on c.c_id = s.c_id group by c.c_id -- 课程分组 order by 3 desc; -- 降序

题目22 题目需求查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 SQL实现 自己的方法 1、课程表和成绩表连接起来，显示所有的课程和成绩信息 select s.s_id ,s.c_id ,s.s_score ,c.c_name from Score s join Course c on s.c_id = c.c_id

2、查出全部的语文成绩 select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '语文' order by s.s_score desc;

3、我们找出语文的第2、3的学生 select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '语文' order by s.s_score desc limit 1, 2;

4、同时求出语文、数学、英语的分数，并且通过union拼接 -- union连接 (select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '语文' order by s.s_score desc limit 1, 2) union (select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '数学' order by s.s_score desc limit 1, 2) union ((select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '英语' order by s.s_score desc limit 1, 2))

5、将上面的结果学生信息表进行连接即可好歹是实现了? -- 最终脚本 -- ！！！！真的需要好好优化下 select s.s_id ,s.s_name ,t.c_name ,t.s_score from Student s join (-- union连接 (select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '语文' order by s.s_score desc limit 1, 2) union (select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '数学' order by s.s_score desc limit 1, 2) union ((select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '英语' order by s.s_score desc limit 1, 2)))t -- 临时表t on s.s_id = t.s_id

和第25题相同的方法 1、以语文为例，首先我们找出前3名的成绩（包含相同的成绩） -- 语文 select a.s_id ,a.c_id ,a.s_score -- 3、此时a表的成绩就是我们找的 from Score a join Score b on a.c_id = b.c_id and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数，要带上等号 and a.c_id="01" group by 1,2 having count(b.s_id) <= 3 -- 2、b中的个数至少有3个，应对分数相同的情形 order by 3 desc limit 1,2

-- 语文 select a.s_id ,a.c_id ,a.s_score -- 3、此时a表的成绩就是我们找的 from Score a join Score b on a.c_id = b.c_id and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数，要带上等号 and a.c_id="01" group by 1,2 having count(b.s_id) <= 3 -- 2、b中的个数至少有3个，应对分数相同的情形 order by 3 desc limit 1,2; -- 取得第2、3名在通过数学和英语的类似操作得到2、3名的成绩，再进行拼接即可 题目23 题目需求统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[0-60]及所占百分比 SQL实现 自己的方法 1、如何对每个成绩进行分组展示：ABCD代表相应的等级 select c_id ,s_score ,case when s_score >= 85 and s_score<= 100 then 'A' -- 大小关系必须分两次写，一次写的话MySQL无法识别 when 70 <= s_score and s_score < 85 then 'B' when 60 <= s_score and s_score < 70 then 'C' when 0 <= s_score and s_score < 60 then 'D' else '其他' end as 'category' from Score s;

2、将两个表关联起来展示数据 -- 1、查看全部课程和成绩信息 select s.c_id ,c.c_name ,s.s_score ,case when s.s_score >= 85 and s.s_score<= 100 then 'A' -- 大小关系必须分两次写，一次写的话MySQL无法识别 when 70 <= s.s_score and s.s_score < 85 then 'B' when 60 <= s.s_score and s.s_score < 70 then 'C' when 0 <= s.s_score and s.s_score < 60 then 'D' else '其他' end as 'category' from Score s join Course c on s.c_id = c.c_id;

3、完整代码 select s.c_id 编号 ,c.c_name 科目 ,sum(case when s.s_score >= 85 and s.s_score<= 100 then 1 else 0 end) "[85,100]人数" ,round(100 * (sum(case when s.s_score >= 85 and s.s_score<= 100 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[85,100]百分比' ,sum(case when s.s_score >= 70 and s.s_score<= 85 then 1 else 0 end) "[70,85]人数" ,round(100 * (sum(case when s.s_score >= 70 and s.s_score<= 85 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[70,85]百分比' ,sum(case when s.s_score >= 60 and s.s_score<= 70 then 1 else 0 end) "[60,70]人数" ,round(100 * (sum(case when s.s_score >= 60 and s.s_score<= 70 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[60,70]百分比' ,sum(case when s.s_score >= 0 and s.s_score<= 60 then 1 else 0 end) "[0,60]人数" ,round(100 * (sum(case when s.s_score >= 0 and s.s_score<= 60 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[0,60]百分比' from Score s left join Course c on s.c_id = c.c_id group by s.c_id, c.c_name

参考方法 1、先统计每个阶段的人数和占比 select c_id ,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100' ,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*)), 2) '占比' from Score group by c_id; -- 分课程统计总数和占比 -- 方式2 select c_id ,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100' ,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(case when s_score then 1 else 0 end)), 2) '占比' -- 不同count(*) from Score group by c_id;

注意对比：

2、我们将4种情况同时查出来 select c_id ,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100' ,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*)), 2) '[85,100]占比' ,sum(case when s_score > 70 and s_score <=85 then 1 else 0 end) as '70-85' ,round(100 * (sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end) / count(*)), 2) '[70,85]占比' ,sum(case when s_score > 60 and s_score <=70 then 1 else 0 end) as '60-70' ,round(100 * (sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) / count(*)), 2) '[60,70]占比' ,sum(case when s_score >=0 and s_score <=60 then 1 else 0 end) as '0-60' ,round(100 * (sum(case when s_score > 0 and s_score <= 60 then 1 else 0 end) / count(*)), 2) '[0,60]占比' from Score group by c_id; -- 分课程统计总数和占比

3、将科目名称连接起来 -- 整体和自己的方法是类似的 select s.c_id ,c.c_name ,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100' ,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*)), 2) '[85,100]占比' ,sum(case when s_score > 70 and s_score <=85 then 1 else 0 end) as '70-85' ,round(100 * (sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end) / count(*)), 2) '[70,85]占比' ,sum(case when s_score > 60 and s_score <=70 then 1 else 0 end) as '60-70' ,round(100 * (sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) / count(*)), 2) '[60,70]占比' ,sum(case when s_score >=0 and s_score <=60 then 1 else 0 end) as '0-60' ,round(100 * (sum(case when s_score > 0 and s_score <= 60 then 1 else 0 end) / count(*)), 2) '[0,60]占比' from Score s left join Course c on s.c_id = c.c_id group by s.c_id, c.c_name; -- 分课程统计总数和占比

题目24 题目需求查询学生的平均成绩及名次 SQL实现 自己的方法 1、先求出每个人的平均分 -- 自己的方法 select sc.s_id ,s.s_name ,round(avg(sc.s_score),2) avg_score from Score sc join Student s on sc.s_id=s.s_id group by sc.s_id,s.s_name

2、我们对上面的结果进行排序！！！MySQL5中是没有rank函数的，需要自己实现排序功能 -- MYSQL5.7中没有rank函数，所以通过自连接实现 select t1.s_id ,t1.s_name ,t1.avg_score ,(select count(distinct t2.avg_score) from (select sc.s_id ,s.s_name ,round(avg(sc.s_score),2) avg_score from Score sc join Student s on sc.s_id=s.s_id group by sc.s_id,s.s_name)t2 -- 临时表t2也是上面的结果 where t2.avg_score >= t1.avg_score ) rank from (select sc.s_id ,s.s_name ,round(avg(sc.s_score),2) avg_score from Score sc join Student s on sc.s_id=s.s_id group by sc.s_id,s.s_name)t1 -- 临时表t1就是上面的结果 order by t1.avg_score desc;

参考方法 select a.s_id -- 学号 ,@i:=@i+1 as '不保留空缺排名' -- 直接i的自加 ,@k:=(case when @avg_score=a.avg_s then @k else @i end) as '保留空缺排名' ,@avg_score:=avg_s as '平均分' -- 表a中的值 from (select s_id ,round(avg(s_score), 2) as avg_s from Score group by s_id order by 2 desc)a -- 表a：平均成绩的排序和学号 ,(select @avg_score:=0, @i:=0, @k:=0)b -- 表b：通过变量设置初始值

实现rank函数 select s.s_name -- 姓名 ,s.s_score -- 成绩 ,(select count(distinct t2.s_score) from Score t2 where t2.s_score >= t1.s_score) rank -- 在t2分数大的情况下，统计t2的去重个数 from Score t1 order by t1.s_score desc; -- 分数降序排列举例子来说明这个脚本： 姓名成绩张三89李四90王五78小明98小红60

当t1.s_score=89，满足t2.s_score > = t1.s_score的有98，90和89，此时count(distinct t2.s_score) 的个数就是3
当t1.s_score=90，满足t2.s_score > = t1.s_score的有98和90，此时count(distinct t2.s_score) 的个数就是2
当t1.s_score=78，满足t2.s_score > = t1.s_score的有98、90、89和78，此时count(distinct t2.s_score) 的个数就是4
当t1.s_score=98，满足t2.s_score > = t1.s_score的只有98，此时count(distinct t2.s_score) 的个数就是1
当t1.s_score=60，满足t2.s_score > = t1.s_score的有89、90、78、98、60，此时count(distinct t2.s_score) 的个数就是5

通过上面的步骤，我们发现：t1中每个分数对应的个数就是它的排名 题目25 题目需求查询各科成绩前三名的记录 SQL实现 自己的方法 1、首先我们找出语文的前3名 select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '语文' order by s.s_score desc -- 降序之后取出前3条记录 limit 3;

2、通过同样的方法我们可以求出数学和英语的前3条记录，然后通过union进行联结，有待优化? -- 自己的脚本 (select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '语文' order by s.s_score desc -- 降序之后取出前3条记录 limit 3) union (select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '数学' order by s.s_score desc limit 3) union (select s.s_id, s.s_score, c.c_name from Score s join Course c on s.c_id = c.c_id where c.c_name = '英语' order by s.s_score desc limit 3)

参考方法 通过Score表的自连接，表a中的值小于表b中的值，排序之后我们取前3 select a.s_id ,a.c_id ,a.s_score -- 取出a中的成绩 from Score a join Score b on a.c_id = b.c_id and a.s_score <= b.s_score -- 表b中的成绩大 group by 1,2,3 having count(b.s_id) = 3 order by 2, 3 desc; 我们通过语文这个科目来理解上面的代码：前3名是80，80，76

-- 语文 select a.s_id ,a.c_id ,a.s_score -- a表的成绩 from Score a join Score b on a.c_id = b.c_id and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数，要带上等号 group by 1,2,3 having count(b.s_id) <= 3 -- 2、b中的个数至少有3个，应对分数相同的情形 order by 2, 3 desc; -- 课程（2）的升序，成绩（）3的降序

题目26 题目需求查询每门课被选修的学生数 SQL实现 select c.c_id ,c.c_name ,count(s.s_id) from Course c join Score s on c.c_id = s.c_id group by c.c_id;

题目27 题目需求查询出只有两门课程的全部学生的学号和姓名 SQL实现 having条件是分组之后在执行的，where语句是分组前先执行的 select s.s_id ,s.s_name from Student s join Score sc on s.s_id = sc.s_id group by 1,2 having count(sc.c_id) = 2;

题目28 题目需求查询男女生人数 SQL实现先看看数据：男女人数都是4

-- 自己的方法 select count(case when s_sex='男' then 1 end) as '男' ,count(case when s_sex='女' then 1 end) as '女' from Student; -- 参考方法 select s_sex ,count(s_sex) as `人数` from Student group by s_sex;

题目29 题目需求查询名字中含有风字的学生信息 SQL实现先看看哪些同学的名字中有风

-- 模糊匹配：我们在两边都加上了%，考虑的是姓或者名字含有风，虽然风姓很少见 select * from Student where s_name like "%风%";

题目30 题目需求查询同名同性的学生名单，并统计同名人数 SQL实现 1、先看看班级的学生信息

现有的数据中没有同名的学生，但是当班级人数增多的时候很有可能在班级上出现同名的学生 2、假设有同名同性的学生 select a.s_name ,a.s_sex ,count(*) from Student a -- 同一个表的自连接 join Student b on a.s_id != b.s_id -- 连接的时候不能是同一个人：学号保证，每个人的学号是唯一的，其他字段都可能重复 and a.s_sex = b.s_sex -- 性别相同 and a.s_name = b.s_name -- 名字相同 group by 1,2;

题目31 题目需求查询1990年出生的学生信息 SQL实现 select * from Student where s_birth like '1990%'; -- 模糊匹配

题目32 题目需求查询每门课程的平均成绩，结果按平均成绩降序排列；平均成绩相同时，按课程编号c_id升序排列 SQL实现 -- 自己的方法 select c_id ,round(avg(s_score),2) avg_score from Score group by 1 order by 2 desc, c_id; -- 指定字段和排序方法

如果想带上课程的名称，需要和Course表进行联结 -- 自己的方法 select c.c_id ,c.c_name ,round(avg(sc.s_score),2) avg_score from Score sc join Course c on sc.c_id = c.c_id group by 1,2 order by 3 desc, c.c_id; -- 指定字段和排序方法

题目33 题目需求查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 SQL实现 -- 自己的方法 select sc.s_id ,s.s_name ,round(avg(sc.s_score),2) avg_score from Score sc join Student s on sc.s_id = s.s_id group by sc.s_id,s.s_name having avg_score >= 85;

题目34 题目需求查询课程名称为数学，且分数低于60的学生姓名和分数 SQL实现 select s.s_name ,sc.s_score from Score sc -- 成绩表 join Student s -- 学生信息表 on sc.s_id = s.s_id join Course c -- 课程表，指定数学 on sc.c_id = c.c_id where c.c_name = '数学' and sc.s_score < 60; -- 指定成绩

看看真正的数据，的确只有一个人满足

题目35 题目需求查询所有学生的课程及分数情况 SQL实现 select s.s_id ,s.s_name ,sum(case c.c_name when '语文' then sc.s_score else 0 end) as '语文' -- 语文分数 ,sum(case c.c_name when '数学' then sc.s_score else 0 end) as '数学' ,sum(case c.c_name when '英语' then sc.s_score else 0 end) as '英语' ,sum(sc.s_score) as '总分' -- 每个人的总分 from Student s left join Score sc on s.s_id = sc.s_id left join Course c on sc.c_id = c.c_id group by s.s_id, s.s_name; -- 学号和姓名的分组

题目36 题目需求查询任何一门课程成绩在70分以上的姓名、课程名称和分数 SQL实现 select s.s_name ,c.c_name ,sc.s_score from Score sc -- 成绩表 join Student s -- 学生信息表 on sc.s_id = s.s_id join Course c -- 课程表 on sc.c_id = c.c_id where sc.s_score > 70 group by s.s_name, c.c_name, sc.s_score;

题目37 题目需求查询不及格的课程 SQL实现 select sc.c_id ,c.c_name ,sc.s_score from Score sc join Course c on sc.c_id = c.c_id where sc.s_score < 60;

题目38 题目需求查询课程编号为01且课程成绩大于等于80的学生的学号和姓名 SQL实现 select sc.s_id ,s.s_name ,sc.s_score from Score sc -- 成绩表 join Student s -- 学生信息表 on sc.s_id = s.s_id join Course c -- 课程表 on sc.c_id = c.c_id where c.c_id = 01 and sc.s_score >= 80;

题目39 题目需求每门课程的学生人数 SQL实现 select c_id ,count(s_id) from Score group by c_id;

如果想连接到课程名称： -- 报错！！！ select sc.c_id ,c.c_name ,count(sc.s_id) from Score sc join Course c group by sc.c_id; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.c.c_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决的方法是将我们之前的结果作为临时表和Course表来连接查询： select c.c_name 课程名称 ,c.c_id 课程编号 ,t.num 人数 from Course c join(select c_id ,count(s_id) num from Score group by c_id)t -- 上面结果的临时表 on c.c_id = t.c_id;

题目40 题目需求查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩 SQL实现 1、我们先找出张三老师教了哪些课程 select c.c_id ,c.c_name from Course c join Teacher t on c.t_id = t.t_id where t.t_name = '张三';

2、找出哪些人修了数学 select sc.s_id ,sc.s_score from Score sc left join Course c on sc.c_id = c.c_id left join Teacher t on c.t_id = t.t_id where t.t_name = '张三';

3、通过max函数找出成绩的最高分 select max(sc.s_score) from Score sc left join Course c on sc.c_id = c.c_id left join Teacher t on c.t_id = t.t_id where t.t_name = '张三';

4、连接Student表，找出学生信息 select s.* ,sc.s_score ,sc.c_id ,c.c_name from Student s left join Score sc on s.s_id = sc.s_id left join Course c on sc.c_id = c.c_id where sc.s_score in (select max(sc.s_score) -- 最大值90分的确定 from Score sc left join Course c on sc.c_id = c.c_id left join Teacher t on c.t_id = t.t_id where t.t_name = '张三');

题目41 题目需求查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 SQL实现 select a.s_id ,a.c_id ,a.s_score from Score a join Score b on a.c_id != b.c_id and a.s_score = b.s_score and a.s_id != b.s_id;

我们对学号还需要去重下： select distinct a.s_id ,a.c_id ,a.s_score from Score a join Score b on a.c_id != b.c_id and a.s_score = b.s_score and a.s_id != b.s_id;

再看看原始的数据中是否符合要求：

题目42 题目需求查询每门功成绩最好的前两名 SQL实现 自己的方法 还需要好好优化的? -- 先找出语文的前2名同学 select c.c_id ,sc.s_id ,sc.s_score from Score sc join Course c on sc.c_id = c.c_id where c.c_name = '语文' -- 改成数学和英语即可求出相应的信息 order by sc.s_score desc limit 2;

将3门学科的信息进行拼接即可求出答案： -- 最终脚本 (select c.c_id ,sc.s_id ,sc.s_score from Score sc join Course c on sc.c_id = c.c_id where c.c_name = '语文' order by sc.s_score desc limit 2) union (select c.c_id ,sc.s_id ,sc.s_score from Score sc join Course c on sc.c_id = c.c_id where c.c_name = '数学' order by sc.s_score desc limit 2) union (select c.c_id ,sc.s_id ,sc.s_score from Score sc join Course c on sc.c_id = c.c_id where c.c_name = '英语' order by sc.s_score desc limit 2)

参考方法（好方法） 如何解决前几名排序的问题???太牛了 select a.c_id ,a.s_id ,a.s_score from Score a where (select count(1) -- count(1)类似count(*)：统计表b中分数大的数量 from Score b where b.c_id=a.c_id -- 课程相同 and b.s_score >= a.s_score) <= 2 -- 前2名 order by a.c_id;

首先我们看看真实的数据，我们以01课程来进行解释上面的代码：

符合要求count(1)<=2的只有两种情况

还需要好好理解下? 题目43 题目需求统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 SQL实现 select c_id ,count(s_score) num from Score group by c_id having num > 5 order by num desc, c_id;

题目44 题目需求检索至少选修两门课程的学生学号 SQL实现结果显示全部满足要求 select s_id ,count(*) num from Score group by s_id having num >= 2;

题目45 题目需求查询选修了全部课程的学生信息 SQL实现自己的方法 1、全部的课程数目num select count(*) from Course; -- 总共3门

2、从Score表分组统计每个人的课程数目，满足是3的学生信息 select s_id ,count(c_id) num -- 课程数目 from Score group by s_id having num in (select count(*) from Course); --满足全部课程

3、我们找出上面结果中的学生信息即可 select s.* ,count(c_id) num -- 课程数目 from Score sc join Student s on sc.s_id = s.s_id group by s.s_id having num in (select count(*) from Course); --满足全部课程

参考方法 select * -- 3、s_id对应的学生信息 from Student where s_id in(select s_id -- 2、最大课程数对应的s_id from Score group by s_id having count(*)=(select count(*) from Course) -- 1、全部课程数 ) 题目46 题目需求查询各学生的年龄：按照出生日期来算，当前月日 < 出生年月的月日则，年龄减1 参考资料 SQL实现 自己的方法 -- 自己的方法 select * ,case when dayofyear(now()) >= dayofyear(s_birth) then year(now()) - year(s_birth) when dayofyear(now()) < dayofyear(s_birth) then year(now()) - year(s_birth) - 1 else 'other' end as 'age' from Student;

参考方法 select s_name ,s_birth ,date_format(now(), '%Y') - date_format(s_birth, '%Y') - (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end) as age -- 当前日期大，说明已经过生了，年龄正常；反之说明今年还没有到年龄-1 from Student;

如何返回年份/日期通过date_format函数能够指定返回的数据 -- 两个方法 select year(now()); select date_format(now(), '%Y');

返回具体的日期：

题目47 题目需求查询本周过生日的学生 select week(now()); -- 47

DAYOFWEEK(date)

返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)，符合国内标准

WEEKDAY(date)

返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)，国外标准 SQL实现 自己的方法 select * from Student where week(s_birth) = week(now());

参考方法 select * from Student where week(date_format(now(),'%Y%m%d')) = week(s_birth); -- 方式1 select * from student where yearweek(s_birth) = yearweek(date_format(now(),'%Y%m%d')); -- 方式2 题目48 题目需求查询下周过生日的学生 SQL实现 -- 自己的方法 select * from Student where week(s_birth) = week(now()) + 1; -- 往前推1周 -- 参考方法 select * from Student where week(date_format(now(),'%Y%m%d')) + 1= week(s_birth);

边界问题 如果现在刚好的是今年的最后一个周，那么下周就是明年的第一个周，我们如何解决这个问题呢？？改进后的脚本： -- 自己的方法 select * from Student where mod(week(now()), 52) + 1 = week(s_birth); 当现在刚好是第52周，那么mod函数的结果是0，则说明出生的月份刚好是明年的第一周 题目49 题目需求查询本月过生的同学

SQL实现 -- 自己的方法 select * from Student where month(s_birth) = month(now()); -- 参考方法 select * from Student where month(date_format(now(), '%Y%m%d')) = month(s_birth);

返回的是空值，是因为数据本身就没有在11月份出生的同学

题目50 题目需求查询下月过生日的同学 SQL实现 -- 自己的方法 select * from Student where month(s_birth) = month(now()) + 1; -- 推迟一个月 -- 参考方法 select * from Student where month(date_format(now(), '%Y%m%d')) + 1= month(s_birth);

边界问题假设现在是12月份，那么下个月就是明年的1月份，我们如何解决？？？将上面的代码进行改进： select * from Student where mod(month(now()),12) + 1 = month(s_birth);

如果现在是12月份，则mod函数的结果是0，说明生日刚好是1月份

-- 自己的方法：通过自连接实现
select s1.*
from Student s1
where s_id in (
  select s2.s_id from Score s2
  join Score s3
  on s2.s_id=s3.s_id
  where s2.c_id='01' and s3.c_id='02'
);

-- 方法2：直接通过where语句实现
select s1.*
from Student s1, Score s2, Score s3
where s1.s_id=s2.s_id
and s1.s_id=s3.s_id
and s2.c_id=01 and s3.c_id=02;

-- 方法3：两个子查询
-- 1. 先查出学号
select sc1.s_id
from (select * from Score s1 where s1.c_id='01') sc1,
			(select * from Score s1 where s1.c_id='02') sc2
where sc1.s_id=sc2.s_id;

-- 2.找出学生信息
select *
from Student
where s_id in (select sc1.s_id   -- 指定学号是符合要求的
               from (select * from Score s1 where s1.c_id='01') sc1,
               (select * from Score s1 where s1.c_id='02') sc2
where sc1.s_id=sc2.s_id);

SQL实现

查询学过编号为01，并且学过编号为02课程的学生信息

题目需求

题目9

方法2：

select * -- 3. 通过学号找出全部学生信息
from Student
where s_id not in (  -- 2.通过学号取反：学号不在张三老师授课的学生的学号中
  select s_id
  from Score  S
  join Course C
  on S.c_id = C.c_id
  where C.t_id=(select t_id from Teacher where t_name ="张三")  -- 1.查询张三老师的课程
);

-- 方法2：
select *
from Student s1
where s1.s_id not in (
  select s2.s_id from Student s2 join Score s3 on s2.s_id=s3.s_id where s3.c_id in(
    select c.c_id from Course c join Teacher t on c.t_id=t.t_id where t_name="张三"
  )
);

-- 方法3
select s1.*
from Student s1
join Score s2
on s1.s_id=s2.s_id
where s2.c_id not in (
  select c_id from Course c where t_id=(  -- 1. 通过老师找出其对应的课程
    select t_id from Teacher t where t_name="张三"
  )
);

SQL实现

找出没有学过张三老师课程的学生

题目需求

题目8

方法3实现：

方法2来实现：

自己的方法：

-- 方法1：通过张三老师的课程的学生来查找；自己的方法
select * -- 3. 通过学号找出全部学生信息
from Student
where s_id in (
  select s_id    -- 2.通过课程找出对应的学号
  from Score  S
  join Course C
  on S.c_id = C.c_id  -- 课程表和成绩表
  where C.t_id=(select t_id from Teacher where t_name="张三")  -- 1.查询张三老师的课程
);

-- 方法2：通过张三老师的课程来查询
select s1.*
from Student s1
join Score s2
on s1.s_id=s2.s_id
where s2.c_id in (
  select c_id from Course c where t_id=(  -- 1. 通过老师找出其对应的课程
    select t_id from Teacher t where t_name="张三"
  )
)

-- 方法3
select s.* from Teacher t
left join Course c on t.t_id=c.t_id  -- 教师表和课程表
left join Score sc on c.c_id=sc.c_id  -- 课程表和成绩表
left join Student s on s.s_id=sc.s_id  -- 成绩表和学生信息表
where t.t_name='张三';

SQL实现

查询学过张三老师授课的同学的信息

题目需求

题目7

这题怕是最简单的吧?

select count(t_name) from Teacher where t_name like "李%";   -- 通配符

SQL实现

查询“李”姓老师的数量

题目需求

题目6

SQL实现

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

题目需求

题目5

使用null判断

使用ifnull函数

SQL实现2-ifnull函数判断

最后将两个部分的结果连起来即可：通过union方法

没有成绩的同学：

结果为：

平均分小于60

SQL实现1-两种情况连接

查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩（包括有成绩的和无成绩的）

题目要求

题目4

附加题：总分超过200分的同学

SQL实现

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

题目需求

题目3

类比题目1的实现过程

SQL实现

查询"01"课程比"02"课程成绩低的学生的信息及课程分数（题目1是成绩高）

题目要求

题目2

第二种方法实现：

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

云数据库 MySQL

数据库

TDSQL MySQL 版

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。