MySQL 语句练习 + 答案 + 解析 1-20题

备注:信息网上都有,sql 语句自己写的,若有错误,请大家及时指正,谢谢

🎁 这个是百度云,里面有 sql 脚本,使用数据库软件,直接运行脚本就可以了
链接:https://pan.baidu.com/s/1_R-4tOMfEMl86Z_fsJr2CQ
提取码:6666


学生表 student

# 创建表
create table student(sid varchar(6), 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' , '女')

成绩表 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)

课程表 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' , '王五')

四张表之间的关联:
在这里插入图片描述

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

# 解析
SELECT score FROM sc WHERE cid=01;
SELECT score FROM sc WHERE cid=02;

# 答案
# 方法一
SELECT s.*,a.score as score_01,b.score as score_02 
FROM student s,
	(SELECT sid,score FROM sc WHERE cid=01) a,
	(SELECT sid,score FROM sc WHERE cid=02) b
WHERE a.sid=b.sid AND a.score > b.score AND s.sid=a.sid AND b.sid=s.sid;

# 方法二
SELECT c.*, a.score,b.score
FROM sc a, sc b, student c
WHERE a.sid=b.sid AND c.sid=a.sid AND a.cid=01 AND b.cid=02 
AND a.score>b.score;
+------+--------+---------------------+------+----------+----------+
| Sid  | Sname  | Sage                | Ssex | score_01 | score_02 |
+------+--------+---------------------+------+----------+----------+
| 02   | 钱电   | 1990-12-21 00:00:00 ||     70.0 |     60.0 |
| 04   | 李云   | 1990-08-06 00:00:00 ||     50.0 |     30.0 |
+------+--------+---------------------+------+----------+----------+

1.1 查询同时存在" 01 “课程和” 02 "课程的学生的信息及课程分数

# 答案
SELECT s.*,a.score as score_01,b.score as score_02 
FROM student s,
		(SELECT sid,score FROM sc WHERE cid=01) a,
		(SELECT sid,score FROM sc WHERE cid=02) b
WHERE a.sid=b.sid AND s.sid=a.sid AND s.sid=b.sid;
+------+--------+---------------------+------+----------+----------+
| sid  | sname  | sage                | ssex | score_01 | score_02 |
+------+--------+---------------------+------+----------+----------+
| 01   | 赵雷   | 1990-01-01 00:00:00 ||     80.0 |     90.0 |
| 02   | 钱电   | 1990-12-21 00:00:00 ||     70.0 |     60.0 |
| 03   | 孙风   | 1990-05-20 00:00:00 ||     80.0 |     80.0 |
| 04   | 李云   | 1990-08-06 00:00:00 ||     50.0 |     30.0 |
| 05   | 周梅   | 1991-12-01 00:00:00 ||     76.0 |     87.0 |
+------+--------+---------------------+------+----------+----------+

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

# 答案
# 方法一
SELECT *
FROM student a
LEFT JOIN sc b ON a.sid = b.sid AND b.cid = '01'
LEFT JOIN sc c ON a.sid = c.sid AND c.cid = '02'
WHERE b.score IS NOT NULL;  
+------+--------+---------------------+------+------+------+-------+------+------+-------+
| sid  | sname  | sage                | ssex | sid  | cid  | score | sid  | cid  | score |
+------+--------+---------------------+------+------+------+-------+------+------+-------+
| 01   | 赵雷   | 1990-01-01 00:00:00 || 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 02   | 钱电   | 1990-12-21 00:00:00 || 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 03   | 孙风   | 1990-05-20 00:00:00 || 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 04   | 李云   | 1990-08-06 00:00:00 || 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 05   | 周梅   | 1991-12-01 00:00:00 || 05   | 01   |  76.0 | 05   | 02   |  87.0 |
| 06   | 吴兰   | 1992-03-01 00:00:00 || 06   | 01   |  31.0 | NULL | NULL |  NULL |
+------+--------+---------------------+------+------+------+-------+------+------+-------+

# 方法二
SELECT * FROM
   (SELECT * FROM sc WHERE cid=01) a 
LEFT JOIN
   (SELECT * FROM sc WHERE cid=02) b
ON a.sid=b.sid;
+------+------+-------+------+------+-------+
| sid  | cid  | score | sid  | cid  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 05   | 01   |  76.0 | 05   | 02   |  87.0 |
| 06   | 01   |  31.0 | NULL | NULL |  NULL |
+------+------+-------+------+------+-------+

1.2 拓展: 假如方法一,不加 WHERE 条件,会出现很多NULL;于是只要把选修了1号课程的同学查询出来即可,即where子句中的条件为: b.score is not null

SELECT *
FROM student a
LEFT JOIN sc b ON a.sid = b.sid AND b.cid = '01'
LEFT JOIN sc c ON a.sid = c.sid AND c.cid = '02';
+------+--------+---------------------+------+------+------+-------+------+------+-------+
| sid  | sname  | sage                | ssex | sid  | cid  | score | sid  | cid  | score |
+------+--------+---------------------+------+------+------+-------+------+------+-------+
| 01   | 赵雷   | 1990-01-01 00:00:00 || 01   | 01   |  80.0 | 01   | 02   |  90.0 |
| 02   | 钱电   | 1990-12-21 00:00:00 || 02   | 01   |  70.0 | 02   | 02   |  60.0 |
| 03   | 孙风   | 1990-05-20 00:00:00 || 03   | 01   |  80.0 | 03   | 02   |  80.0 |
| 04   | 李云   | 1990-08-06 00:00:00 || 04   | 01   |  50.0 | 04   | 02   |  30.0 |
| 05   | 周梅   | 1991-12-01 00:00:00 || 05   | 01   |  76.0 | 05   | 02   |  87.0 |
| 07   | 郑竹   | 1989-07-01 00:00:00 || NULL | NULL |  NULL | 07   | 02   |  89.0 |
| 06   | 吴兰   | 1992-03-01 00:00:00 || 06   | 01   |  31.0 | NULL | NULL |  NULL |
| 08   | 王菊   | 1990-01-20 00:00:00 || NULL | NULL |  NULL | NULL | NULL |  NULL |

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

# 解析
SELECT sid FROM sc WHERE cid=01; # 找出包含01课程的sid
SELECT * FROM sc WHERE cid=02 AND sid not in 
	(SELECT sid FROM sc WHERE cid=01);
	# 再使用 student ,找出不包含刚才找的(包括课程01的sid),not in

# 答案
SELECT s.*,a.score,a.cid FROM student s,
		(SELECT * FROM sc WHERE cid=02 AND sid not in (SELECT sid FROM sc WHERE cid=01)) a
WHERE s.sid=a.sid

+------+--------+---------------------+------+-------+------+
| sid  | sname  | sage                | ssex | score | cid  |
+------+--------+---------------------+------+-------+------+
| 07   | 郑竹   | 1989-07-01 00:00:00 ||  89.0 | 02   |
+------+--------+---------------------+------+-------+------+

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

# 解析
SELECT sid,AVG(score) as avg_score 
FROM sc GROUP BY sid HAVING avg_score>60;	# 先找出平均成绩大于等于60的学生sid

# 答案
# 方法一
select s.sid, s.sname,a.avg_score
FROM student s
RIGHT JOIN (SELECT sid,AVG(score) as avg_score 
				FROM sc GROUP BY sid HAVING avg_score>60) a
ON s.sid=a.sid;

# 方法二
SELECT s.sid,s.sname,AVG(score) as avg_score 
FROM student s,sc
WHERE s.sid=sc.sid
GROUP BY s.sid,s.sname
HAVING avg_score>=60;
+------+--------+-----------+
| sid  | sname  | avg_score |
+------+--------+-----------+
| 01   | 赵雷   |  89.66667 |
| 02   | 钱电   |  70.00000 |
| 03   | 孙风   |  80.00000 |
| 05   | 周梅   |  81.50000 |
| 07   | 郑竹   |  93.50000 |
+------+--------+-----------+

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

# 解析
SELECT s.* FROM student s;

# 答案
# 方法一
SELECT DISTINCT s.* FROM student s
RIGHT JOIN sc sc
ON s.sid=sc.sid;

# 方法二
SELECT * FROM student WHERE sid IN 
	(SELECT sid FROM sc WHERE score is not NULL);
+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 ||
| 02   | 钱电   | 1990-12-21 00:00:00 ||
| 03   | 孙风   | 1990-05-20 00:00:00 ||
| 04   | 李云   | 1990-08-06 00:00:00 ||
| 05   | 周梅   | 1991-12-01 00:00:00 ||
| 06   | 吴兰   | 1992-03-01 00:00:00 ||
| 07   | 郑竹   | 1989-07-01 00:00:00 ||
+------+--------+---------------------+------+

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

# 答案
SELECT s.sid,s.sname,COUNT(cid) AS 选课总数, SUM(score) as 总成绩
FROM student s 
LEFT JOIN sc
on s.sid=sc.sid
GROUP BY s.sid,s.sname;

# 拓展:去掉没有总成绩的同学,因为左连接,有时候没数据会 Null
SELECT s.sid,s.sname,COUNT(cid) AS 选课总数, SUM(score) as 总成绩
FROM student s 
LEFT JOIN sc
on s.sid=sc.sid
WHERE score is not NULL
GROUP BY s.sid,s.sname;
+------+--------+--------------+-----------+
| sid  | sname  | 选课总数     | 总成绩    |
+------+--------+--------------+-----------+
| 01   | 赵雷   |            3 |     269.0 |
| 02   | 钱电   |            3 |     210.0 |
| 03   | 孙风   |            3 |     240.0 |
| 04   | 李云   |            3 |     100.0 |
| 05   | 周梅   |            2 |     163.0 |
| 06   | 吴兰   |            2 |      65.0 |
| 07   | 郑竹   |            2 |     187.0 |
| 08   | 王菊   |            0 |      NULL |
+------+--------+--------------+-----------+

4.1 查有成绩的学生信息

首先了解:CASE WHEN THEN ELSE END

SELECT
    case                   -------------如果
    when sex='1' then '男' -------------sex='1',则返回值'男'
    when sex='2' then '女' -------------sex='2',则返回值'女'
    else '其他'                 -------------其他的返回'其他’
    end                    -------------结束
from   sys_user            --------整体理解: 在sys_user表中如果sex='1',则返回值'男'如果sex='2',则返回值'女' 否则返回'其他’

用法一:

SELECT 
         CASE WHEN STATE = '1' THEN '成功' 
              WHEN STATE = '2' THEN '失败'
         ELSE '其他' END  
         FROM  SYS_SCHEDULER

用法二:

SELECT STATE
             CASE WHEN '1' THEN '成功' 
                  WHEN '2' THEN '失败'
             ELSE '其他' END  
             FROM  SYS_SCHEDULER

参考地址1:https://www.cnblogs.com/vincentbnu/p/9495609.html
参考地址2:https://www.cnblogs.com/anche/p/9038802.html

# 答案
select s.sid, s.sname, count(*) as 选课总数, sum(score) as 总成绩,
    sum(case when cid = 01 then score else null end) as score_01,
    sum(case when cid = 02 then score else null end) as score_02,
    sum(case when cid = 03 then score else null end) as score_03
from student as s, sc
where s.sid = sc.sid
group by s.sid,s.sname;
+------+--------+--------------+-----------+----------+----------+----------+
| sid  | sname  | 选课总数     | 总成绩    | score_01 | score_02 | score_03 |
+------+--------+--------------+-----------+----------+----------+----------+
| 01   | 赵雷   |            3 |     269.0 |     80.0 |     90.0 |     99.0 |
| 02   | 钱电   |            3 |     210.0 |     70.0 |     60.0 |     80.0 |
| 03   | 孙风   |            3 |     240.0 |     80.0 |     80.0 |     80.0 |
| 04   | 李云   |            3 |     100.0 |     50.0 |     30.0 |     20.0 |
| 05   | 周梅   |            2 |     163.0 |     76.0 |     87.0 |     NULL |
| 06   | 吴兰   |            2 |      65.0 |     31.0 |     NULL |     34.0 |
| 07   | 郑竹   |            2 |     187.0 |     NULL |     89.0 |     98.0 |
+------+--------+--------------+-----------+----------+----------+----------+

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

# 答案
SELECT count(tname) FROM teacher WHERE tname LIKE '李%';
+--------------+
| count(tname) |
+--------------+
|            1 |
+--------------+

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

# 解析
SELECT tid,tname FROM teacher;		# 老师id,姓名

SELECT cid,cname,tid FROM course;	# 课程id,课程名,课程的代课老师id

SELECT t.tid,t.tname,c.cid,c.cname
FROM teacher t,course c
WHERE t.tid=c.tid;				    # 所有老师id,名字,课程id,课程名

SELECT sid FROM sc,course,teacher
WHERE teacher.tname='张三' AND course.tid=teacher.tid 
	AND sc.cid=course.cid;	 # 上过张三老师课的学生sid

# 答案
SELECT * FROM student WHERE sid IN 
	(SELECT sid FROM sc,course,teacher
		WHERE teacher.tname='张三' AND course.tid=teacher.tid 
			AND sc.cid=course.cid); # 上过张三老师课的学生信息

# 这个是看别的文章的
# 原作者的写法里面用到了等号=,虽然得到同样的结果,但是这样写不太好
# 因为不确定张三老师是不是只教授一门课(只不过现在的数据量太小了而已)
# in适用于一个或多个返回结果的情况,适应性比等号更广

select * from student
where sid in(select distinct sid from sc
where cid=(select cid from course
where tid=(select tid from teacher where tname='张三')));

+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 ||
| 02   | 钱电   | 1990-12-21 00:00:00 ||
| 03   | 孙风   | 1990-05-20 00:00:00 ||
| 04   | 李云   | 1990-08-06 00:00:00 ||
| 05   | 周梅   | 1991-12-01 00:00:00 ||
| 07   | 郑竹   | 1989-07-01 00:00:00 ||
+------+--------+---------------------+------+

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

# 解析
SELECT sid FROM sc GROUP BY sid HAVING COUNT(cid)>=3;	
# 课程,成绩表中,查询大于等于的学生

# 答案
SELECT * FROM student WHERE sid NOT IN 
	(SELECT sid FROM sc GROUP BY sid HAVING COUNT(cid)>=3);	
		# 除了大于等于三 就是课程不够的学生
+------+--------+---------------------+------+
| sid  | sname  | sage                | ssex |
+------+--------+---------------------+------+
| 05   | 周梅   | 1991-12-01 00:00:00  ||
| 06   | 吴兰   | 1992-03-01 00:00:00  ||
| 07   | 郑竹   | 1989-07-01 00:00:00  ||
| 08   | 王菊   | 1990-01-20 00:00:00  ||
+------+--------+---------------------+------+

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

# 解析
SELECT cid FROM sc WHERE sid=01;	# 先找出01同学所学的课程

SELECT DISTINCT sid FROM sc WHERE cid IN 
	(SELECT cid FROM sc WHERE sid=01);  # 再找,哪个学生sid,在学这门课

# 答案
SELECT * FROM student WHERE sid IN
	(SELECT DISTINCT sid FROM sc WHERE cid IN (
			SELECT cid FROM sc WHERE sid=01));	# 最后找到学生信息
+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 ||
| 02   | 钱电   | 1990-12-21 00:00:00 ||
| 03   | 孙风   | 1990-05-20 00:00:00 ||
| 04   | 李云   | 1990-08-06 00:00:00 ||
| 05   | 周梅   | 1991-12-01 00:00:00 ||
| 06   | 吴兰   | 1992-03-01 00:00:00 ||
| 07   | 郑竹   | 1989-07-01 00:00:00 ||
+------+--------+---------------------+------+

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

# 解析
SELECT cid FROM sc WHERE sid=01;	# 先找出01同学所学的课程

SELECT sid FROM sc WHERE cid IN 
	(SELECT cid FROM sc WHERE sid=01);	# 再找,哪个学生sid,在学这门课

# 答案
SELECT * FROM student WHERE sid IN
	(SELECT sid FROM sc WHERE cid IN 
			(SELECT cid FROM sc WHERE sid=01) 
				AND sid!=01 
				GROUP BY sid HAVING COUNT(cid)>=3);
+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 02   | 钱电   | 1990-12-21 00:00:00 ||
| 03   | 孙风   | 1990-05-20 00:00:00 ||
| 04   | 李云   | 1990-08-06 00:00:00 ||
+------+--------+---------------------+------+

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

# 解析
SELECT tid FROM teacher WHERE tname='张三'; # 先找张三老师的 tid 01	

SELECT cid FROM course WHERE 
	tid=(SELECT tid FROM teacher WHERE tname='张三'); 
		# 找张三老师教的课 cid 02

SELECT sid FROM sc WHERE cid=
	(SELECT cid FROM course WHERE tid=
		(SELECT tid FROM teacher WHERE tname='张三'));	
			# 找出学过 cid 02 课程的学生

# 答案
# 方法一
SELECT sname FROM student WHERE sid not in 
	(SELECT sid FROM sc WHERE cid=
			(SELECT cid FROM course WHERE tid=
					(SELECT tid FROM teacher WHERE tname='张三')));	
					# 排除学过 02 课程的学生并获取姓名

# 方法二
SELECT sname FROM student
WHERE sname NOT IN (
	SELECT s.sname
	FROM student s,course c,teacher t,sc
	WHERE s.sid=sc.sid AND sc.cid=c.cid 
		AND c.tid=t.tid AND t.tname='张三');
	
+--------+
| sname  |
+--------+
| 吴兰   |
| 王菊   |
+--------+

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

# 解析
SELECT AVG(score) FROM sc WHERE sid=04;	# 根据sid求平均成绩

SELECT sid FROM sc 
WHERE score<=60 
GROUP BY sid HAVING COUNT(*)>=2;	# 找出学生sid 成绩至少两门不及格

# 答案
# 方法一
SELECT s.sid,s.sname,AVG(sc.score) FROM student s,sc 
WHERE s.sid=sc.sid AND s.sid IN 
	(SELECT sid FROM sc WHERE score<=60 
		GROUP BY sid HAVING COUNT(*)>=2) 
GROUP BY s.sid,s.sname;

# 方法二
select a.sid,a.sname,AVG(b.score) 
from student a 
left join sc b 
on a.sid = b.sid where a.sid in
	(select sid from sc where score<60 
	GROUP BY sid having count(*)>=2) 
GROUP BY a.sid,a.sname;
+------+--------+------------+
| sid  | sname  | avg(score) |
+------+--------+------------+
| 04   | 李云   |   33.33333 |
| 06   | 吴兰   |   32.50000 |
+------+--------+------------+

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

# 解析
SELECT sid,score FROM sc WHERE cid=01 GROUP BY cid,score,sid;	
# 找出01课程的分数score 学生sid

# 答案
SELECT s.*,st.score FROM student s
LEFT JOIN (SELECT sid,score FROM sc WHERE cid=01 GROUP BY cid,score,sid) st
ON s.sid=st.sid WHERE st.score<=60 ORDER BY st.score desc;

# 方法二
SELECT s.*,score
FROM student s,sc
WHERE cid=01 and score <=60 AND s.sid=sc.sid
ORDER BY score DESC;
+------+--------+---------------------+------+-------+
| Sid  | Sname  | Sage                | Ssex | score |
+------+--------+---------------------+------+-------+
| 04   | 李云   | 1990-08-06 00:00:00 ||  50.0 |
| 06   | 吴兰   | 1992-03-01 00:00:00 ||  31.0 |
+------+--------+---------------------+------+-------+

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

# 解析
SELECT sid,AVG(score) FROM sc GROUP BY sid ORDER BY AVG(score) DESC;	
# 每个人的平均成绩降序排序

SELECT sid,score FROM sc WHERE cid=01; 
SELECT sid,score FROM sc WHERE cid=02; 
SELECT sid,score FROM sc WHERE cid=03;

# 答案
select sid,
    sum(case when cid=01 then score else null end) as score_01,
    sum(case when cid=02 then score else null end) as score_02,
    sum(case when cid=03 then score else null end) as score_03,
    avg(score)
from sc group by sid
order by avg(score) desc;
+------+----------+----------+----------+------------+
| sid  | score_01 | score_02 | score_03 | avg(score) |
+------+----------+----------+----------+------------+
| 07   |     NULL |     89.0 |     98.0 |   93.50000 |
| 01   |     80.0 |     90.0 |     99.0 |   89.66667 |
| 05   |     76.0 |     87.0 |     NULL |   81.50000 |
| 03   |     80.0 |     80.0 |     80.0 |   80.00000 |
| 02   |     70.0 |     60.0 |     80.0 |   70.00000 |
| 04   |     50.0 |     30.0 |     20.0 |   33.33333 |
| 06   |     31.0 |     NULL |     34.0 |   32.50000 |
+------+----------+----------+----------+------------+

14. 查询各科成绩最高分、最低分和平均分,以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率(及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

# 解析
SELECT cid,MAX(score) AS 最高分,MIN(score) AS 最低分,AVG(score) AS 平均分 
FROM sc GROUP BY cid;	# 找出每个课程的 最高分 最低分 平均分

# 答案
select c.cid as 课程号, c.cname as 课程名称, count(*) as 选修人数,
    max(score) as 最高分, min(score) as 最低分, avg(score) as 平均分,
    sum(case when score >= 60 then 1 else 0 end)/count(*) as 及格率,
    sum(case when score >= 70 and score < 80 then 1 else 0 end)/count(*) as 中等率,
    sum(case when score >= 80 and score < 90 then 1 else 0 end)/count(*) as 优良率,
    sum(case when score >= 90 then 1 else 0 end)/count(*) as 优秀率
from sc, course c
where c.cid = sc.cid
group by c.cid,c.cname
order by count(*) desc, c.cid asc;
+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 课程号    | 课程名称      | 选修人数      | 最高分     | 最低分    | 平均分     | 及格率    | 中等率    | 优良率     | 优秀率     |
+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 01        | 语文         |            6 |      80.0 |      31.0 |  64.50000 |    0.6667 |    0.3333 |    0.3333 |    0.0000 |
| 02        | 数学         |            6 |      90.0 |      30.0 |  72.66667 |    0.8333 |    0.0000 |    0.5000 |    0.1667 |
| 03        | 英语         |            6 |      99.0 |      20.0 |  68.50000 |    0.6667 |    0.0000 |    0.3333 |    0.3333 |
+-----------+--------------+--------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

15. 按平均成绩进行排序,显示总排名和各科排名,Score 重复时保留名次空缺

这道题,真是蛋疼。。慢慢细品吧 ~ ~ ~,坚持,我弄这个15题,搜了很多资料。。花了很长时间。。

思路一:分组查询各科成绩,有成绩,但是没名次~

SELECT sid,cid,score FROM sc GROUP BY cid,sid,score; 

+------+------+-------+
| sid  | cid  | score |
+------+------+-------+
| 01   | 01   |  80.0 |
| 02   | 01   |  70.0 |
| 03   | 01   |  80.0 |
| 04   | 01   |  50.0 |
| 05   | 01   |  76.0 |
| 06   | 01   |  31.0 |
| 01   | 02   |  90.0 |
| 02   | 02   |  60.0 |
| 03   | 02   |  80.0 |
| 04   | 02   |  30.0 |
| 05   | 02   |  87.0 |
| 07   | 02   |  89.0 |
| 01   | 03   |  99.0 |
| 02   | 03   |  80.0 |
| 03   | 03   |  80.0 |
| 04   | 03   |  20.0 |
| 06   | 03   |  34.0 |
| 07   | 03   |  98.0 |
+------+------+-------+

思路二:先给sc表起别名 a,b;然后使用 LEFT JOIN ON a.score<b.score;
循环a表中的全部行, 与b表第一行比, 显示出满足条件的行, 行的列包括b第一行内容
循环a表中的全部行, 与b表中第二行比, 显示出满足条件的行, 行的列包括b第二行内容
……
循环a表中的全部行, 与b表中最后一行比

SELECT * 
FROM sc AS a
LEFT JOIN sc AS b
ON a.cid=b.cid AND a.score<b.score;


+------+------+-------+------+------+-------+
| sid  | cid  | score | sid  | cid  | score |
+------+------+-------+------+------+-------+
| 02   | 01   |  70.0 | 01   | 01   |  80.0 |
| 04   | 01   |  50.0 | 01   | 01   |  80.0 |
| 05   | 01   |  76.0 | 01   | 01   |  80.0 |
| 06   | 01   |  31.0 | 01   | 01   |  80.0 |
| 02   | 02   |  60.0 | 01   | 02   |  90.0 |
| 03   | 02   |  80.0 | 01   | 02   |  90.0 |
| 04   | 02   |  30.0 | 01   | 02   |  90.0 |
| 05   | 02   |  87.0 | 01   | 02   |  90.0 |
| 07   | 02   |  89.0 | 01   | 02   |  90.0 |
| 02   | 03   |  80.0 | 01   | 03   |  99.0 |
| 03   | 03   |  80.0 | 01   | 03   |  99.0 |
| 04   | 03   |  20.0 | 01   | 03   |  99.0 |
| 06   | 03   |  34.0 | 01   | 03   |  99.0 |
| 07   | 03   |  98.0 | 01   | 03   |  99.0 |
| 04   | 01   |  50.0 | 02   | 01   |  70.0 |
| 06   | 01   |  31.0 | 02   | 01   |  70.0 |
| 04   | 02   |  30.0 | 02   | 02   |  60.0 |
| 04   | 03   |  20.0 | 02   | 03   |  80.0 |
| 06   | 03   |  34.0 | 02   | 03   |  80.0 |
| 02   | 01   |  70.0 | 03   | 01   |  80.0 |
| 04   | 01   |  50.0 | 03   | 01   |  80.0 |
| 05   | 01   |  76.0 | 03   | 01   |  80.0 |
| 06   | 01   |  31.0 | 03   | 01   |  80.0 |
| 02   | 02   |  60.0 | 03   | 02   |  80.0 |
| 04   | 02   |  30.0 | 03   | 02   |  80.0 |
| 04   | 03   |  20.0 | 03   | 03   |  80.0 |
| 06   | 03   |  34.0 | 03   | 03   |  80.0 |
| 06   | 01   |  31.0 | 04   | 01   |  50.0 |
| 02   | 01   |  70.0 | 05   | 01   |  76.0 |
| 04   | 01   |  50.0 | 05   | 01   |  76.0 |
| 06   | 01   |  31.0 | 05   | 01   |  76.0 |
| 02   | 02   |  60.0 | 05   | 02   |  87.0 |
| 03   | 02   |  80.0 | 05   | 02   |  87.0 |
| 04   | 02   |  30.0 | 05   | 02   |  87.0 |
| 04   | 03   |  20.0 | 06   | 03   |  34.0 |
| 02   | 02   |  60.0 | 07   | 02   |  89.0 |
| 03   | 02   |  80.0 | 07   | 02   |  89.0 |
| 04   | 02   |  30.0 | 07   | 02   |  89.0 |
| 05   | 02   |  87.0 | 07   | 02   |  89.0 |
| 02   | 03   |  80.0 | 07   | 03   |  98.0 |
| 03   | 03   |  80.0 | 07   | 03   |  98.0 |
| 04   | 03   |  20.0 | 07   | 03   |  98.0 |
| 06   | 03   |  34.0 | 07   | 03   |  98.0 |
| 01   | 01   |  80.0 | NULL | NULL |  NULL |
| 01   | 02   |  90.0 | NULL | NULL |  NULL |
| 01   | 03   |  99.0 | NULL | NULL |  NULL |
| 03   | 01   |  80.0 | NULL | NULL |  NULL |
+------+------+-------+------+------+-------+

思路三:将上面比较之后的结果,进行排序,使用 sid 和 cid,使用这两个参数,可以让数据看起来更有顺序,没有写排序参数 ASC 或 DESC,默认 ASC 升序排序

SELECT * 
FROM sc AS a
LEFT JOIN sc AS b
ON a.cid=b.cid AND a.score<b.score 
ORDER BY a.sid,a.cid;
+------+------+-------+------+------+-------+
| sid  | cid  | score | sid  | cid  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | NULL | NULL |  NULL |
| 01   | 02   |  90.0 | NULL | NULL |  NULL |
| 01   | 03   |  99.0 | NULL | NULL |  NULL |
| 02   | 01   |  70.0 | 01   | 01   |  80.0 |
| 02   | 01   |  70.0 | 03   | 01   |  80.0 |
| 02   | 01   |  70.0 | 05   | 01   |  76.0 |
| 02   | 02   |  60.0 | 01   | 02   |  90.0 |
| 02   | 02   |  60.0 | 05   | 02   |  87.0 |
| 02   | 02   |  60.0 | 07   | 02   |  89.0 |
| 02   | 02   |  60.0 | 03   | 02   |  80.0 |
| 02   | 03   |  80.0 | 01   | 03   |  99.0 |
| 02   | 03   |  80.0 | 07   | 03   |  98.0 |
| 03   | 01   |  80.0 | NULL | NULL |  NULL |
| 03   | 02   |  80.0 | 05   | 02   |  87.0 |
| 03   | 02   |  80.0 | 07   | 02   |  89.0 |
| 03   | 02   |  80.0 | 01   | 02   |  90.0 |
| 03   | 03   |  80.0 | 07   | 03   |  98.0 |
| 03   | 03   |  80.0 | 01   | 03   |  99.0 |
| 04   | 01   |  50.0 | 03   | 01   |  80.0 |
| 04   | 01   |  50.0 | 05   | 01   |  76.0 |
| 04   | 01   |  50.0 | 01   | 01   |  80.0 |
| 04   | 01   |  50.0 | 02   | 01   |  70.0 |
| 04   | 02   |  30.0 | 02   | 02   |  60.0 |
| 04   | 02   |  30.0 | 01   | 02   |  90.0 |
| 04   | 02   |  30.0 | 03   | 02   |  80.0 |
| 04   | 02   |  30.0 | 05   | 02   |  87.0 |
| 04   | 02   |  30.0 | 07   | 02   |  89.0 |
| 04   | 03   |  20.0 | 03   | 03   |  80.0 |
| 04   | 03   |  20.0 | 07   | 03   |  98.0 |
| 04   | 03   |  20.0 | 06   | 03   |  34.0 |
| 04   | 03   |  20.0 | 01   | 03   |  99.0 |
| 04   | 03   |  20.0 | 02   | 03   |  80.0 |
| 05   | 01   |  76.0 | 01   | 01   |  80.0 |
| 05   | 01   |  76.0 | 03   | 01   |  80.0 |
| 05   | 02   |  87.0 | 07   | 02   |  89.0 |
| 05   | 02   |  87.0 | 01   | 02   |  90.0 |
| 06   | 01   |  31.0 | 04   | 01   |  50.0 |
| 06   | 01   |  31.0 | 03   | 01   |  80.0 |
| 06   | 01   |  31.0 | 02   | 01   |  70.0 |
| 06   | 01   |  31.0 | 01   | 01   |  80.0 |
| 06   | 01   |  31.0 | 05   | 01   |  76.0 |
| 06   | 03   |  34.0 | 02   | 03   |  80.0 |
| 06   | 03   |  34.0 | 03   | 03   |  80.0 |
| 06   | 03   |  34.0 | 07   | 03   |  98.0 |
| 06   | 03   |  34.0 | 01   | 03   |  99.0 |
| 07   | 02   |  89.0 | 01   | 02   |  90.0 |
| 07   | 03   |  98.0 | 01   | 03   |  99.0 |
+------+------+-------+------+------+-------+

  • 01学生:3科成绩,没有比他高的
  • 02学生:01科目,比他高的(01,03,05)同学;02科目,比他高的(01,05,07,03)同学;03科目,比他高的(01,07)同学

观察上图:

  • 01学生:01课程,分数80,没人比他高,所以,b.score 都是 NULL,那么01 01 80 自然排第一,count(b.score)+1
  • 02学生:01课程,分数70,比他高的3人,所以排名 3+1,也就是count(b.score) 为 3,count(b.score)+1

最终答案:分析到这,我们对比这几个思路的SQL语句,就更明白是如何写出来的

思路一:对课程成绩分组查询

SELECT sid,cid,score FROM sc GROUP BY cid,sid,score

思路二:循环对比,满足条件,同课程下,分数 a.score < b.score

SELECT * 
FROM sc AS a
LEFT JOIN sc AS b
ON a.cid=b.cid AND a.score<b.score;

思路三:将循环对比的中间表,进行排序,a.sid,a.cid

SELECT * 
FROM sc AS a
LEFT JOIN sc AS b
ON a.cid=b.cid AND a.score<b.score 
ORDER BY a.sid,a.cid;

最终答案:排序完,有重复数据,进行分组,记录名次,消除重复

SELECT a.cid,a.sid,a.score,COUNT(b.score)+1 AS rank
FROM sc AS a
LEFT JOIN sc AS b 
ON a.score<b.score AND a.cid=b.cid
GROUP BY a.cid,a.sid,a.score
ORDER BY a.cid,rank ASC;
# score 重复时,保留名次空缺,不难发现,01课程,相同成绩的学生,名次为1,没有2,直接跳3
+------+------+-------+------+
| cid  | sid  | score | rank |
+------+------+-------+------+
| 01   | 01   |  80.0 |    1 |
| 01   | 03   |  80.0 |    1 |
| 01   | 05   |  76.0 |    3 |
| 01   | 02   |  70.0 |    4 |
| 01   | 04   |  50.0 |    5 |
| 01   | 06   |  31.0 |    6 |
| 02   | 01   |  90.0 |    1 |
| 02   | 07   |  89.0 |    2 |
| 02   | 05   |  87.0 |    3 |
| 02   | 03   |  80.0 |    4 |
| 02   | 02   |  60.0 |    5 |
| 02   | 04   |  30.0 |    6 |
| 03   | 01   |  99.0 |    1 |
| 03   | 07   |  98.0 |    2 |
| 03   | 02   |  80.0 |    3 |
| 03   | 03   |  80.0 |    3 |
| 03   | 06   |  34.0 |    5 |
| 03   | 04   |  20.0 |    6 |
+------+------+-------+------+

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

# 答案

SELECT a.cid,a.sid,a.score, count(DISTINCT b.score)+1 AS rank
FROM sc AS a 
LEFT JOIN sc AS b 
ON a.cid=b.cid AND a.score<b.score
GROUP BY a.cid, a.sid,a.score
ORDER BY a.cid, rank ASC;
# score 重复时,合并名词,观察01课程,不难发现,有并列名词,然后又第2,并没有直接跳第三
+------+------+-------+------+
| cid  | sid  | score | rank |
+------+------+-------+------+
| 01   | 03   |  80.0 |    1 |
| 01   | 01   |  80.0 |    1 |
| 01   | 05   |  76.0 |    2 |
| 01   | 02   |  70.0 |    3 |
| 01   | 04   |  50.0 |    4 |
| 01   | 06   |  31.0 |    5 |
| 02   | 01   |  90.0 |    1 |
| 02   | 07   |  89.0 |    2 |
| 02   | 05   |  87.0 |    3 |
| 02   | 03   |  80.0 |    4 |
| 02   | 02   |  60.0 |    5 |
| 02   | 04   |  30.0 |    6 |
| 03   | 01   |  99.0 |    1 |
| 03   | 07   |  98.0 |    2 |
| 03   | 03   |  80.0 |    3 |
| 03   | 02   |  80.0 |    3 |
| 03   | 06   |  34.0 |    4 |
| 03   | 04   |  20.0 |    5 |
+------+------+-------+------+

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

首先了解一下:myql 变量赋值

mysql中变量不用事前申明,在用的时候直接用“@变量名”使用就可以了。

  • 第一种用法:set @num=1; 或set @num:=1; //这里要使用变量来保存数据,直接使用@num变量
  • 第二种用法:select @num:=1; 或 select @num:=字段名 from 表名 where ……
  • 第三种用法:select 字段名1,字段名2 into @变量1,@变量2 from 表名 where …

1、 注意上面两种赋值符号,使用set时可以用“=”或“:=”,但是使用select时必须用“:=赋值”
2、在函数或存储过程或触发器中,在不能使用set的时候推荐第三种,因为第二种会在执行时返回查询结果,这在函数或触发器中会报 “Not allowed to return a result set from a function”错误。而第三种则不会报错。

# 解析
SELECT sid,sum(score) AS total 
FROM sc 
GROUP BY sid 
ORDER BY sum(score) DESC	# 总成绩排序

# 答案
SELECT t.sid, t.total,t.rank FROM 
		(SELECT q.sid,q.total,@crank := IF(@prevRank = q.total,@crank,@incRank) AS rank,
			 @incRank := @incRank +1,
			 @prevRank := q.total
			 FROM (SELECT sid,sum(score) AS total FROM sc GROUP BY sid ORDER BY sum(score) DESC) q,
					  (SELECT @crank :=0, @prevRank :=NULL, @incRank :=1) r) t;	
+------+-------+------+
| sid  | total | rank |
+------+-------+------+
| 01   | 269.0 | 1    |
| 03   | 240.0 | 2    |
| 02   | 210.0 | 3    |
| 06   | 187.0 | 4    |
| 07   | 187.0 | 4    |
| 05   | 163.0 | 6    |
| 04   | 100.0 | 7    |
+------+-------+------+

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

本题的答案是:DENSE_RANK():连续排序,不保留名次空

拓展:RANK()、DENSE_RANK()、ROW_NUMBER()

  • MySQL中没有RANK排名函数,当我们需要查询排名时,只能使用MySQL数据库中的基本查询语句来查询普通排名
  • 我的mysql是5.7版本,好像8版本以后的就有RANK()、DENSE_RANK()
  • 使用别的方法,实现 RANK()、DENSE_RANK()

相同点:RANK()和DENSE_RANK()的是排名函数
不同点:RANK()是跳跃排序,即如果有两条记录重复,接下来是第三级别
如:1 2 2 4,会跳过3
DENSE_RANK()是连续排序,即如果有两条记录重复,接下来是第二级别
如:1 2 2 3

原始插入的数据,都是不同的,于是我们得改,然后看变化!
把成绩表分数改相同;
在这里插入图片描述

RANK():跳跃排序,保留名次空缺

# RANK()
SELECT t.sid, t.total,t.rank FROM 
		(SELECT q.sid,q.total,@crank := IF(@prevRank = q.total,@crank,@incRank) AS rank,
			@incRank := @incRank +1,
			@prevRank := q.total FROM (SELECT sid,sum(score) AS total FROM sc GROUP BY sid ORDER BY sum(score) DESC) q,
			(SELECT @crank :=0, @prevRank :=NULL, @incRank :=1) r) t;		
+------+-------+------+
| sid  | total | rank |
+------+-------+------+
| 01   | 269.0 | 1    |
| 03   | 240.0 | 2    |
| 02   | 210.0 | 3    |
| 06   | 187.0 | 4    |
| 07   | 187.0 | 4    |
| 05   | 163.0 | 6    |
| 04   | 100.0 | 7    |
+------+-------+------+

DENSE_RANK():连续排序,不保留名次空缺

# DENSE_RANK()
SELECT q.sid, q.total,
CASE 
	WHEN @prevRank = q.total THEN @crank
	WHEN @prevRank := q.total THEN @crank := @crank +1
END AS rank
FROM (SELECT sid,sum(score) AS total FROM sc GROUP BY sid ORDER BY sum(score) DESC) q,
(SELECT @crank := 0,@prevRank := NULL) p;
+------+-------+------+
| sid  | total | rank |
+------+-------+------+
| 01   | 269.0 |    1 |
| 03   | 240.0 |    2 |
| 02   | 210.0 |    3 |
| 06   | 187.0 |    4 |
| 07   | 187.0 |    4 |
| 05   | 163.0 |    5 |
| 04   | 100.0 |    6 |
+------+-------+------+

ROW_NUMBER():顺序排序

# 解析
SELECT sid,sum(score) FROM sc GROUP BY sid ORDER BY sum(score) DESC	# 查询所有学生成绩

# 
# 方法一:提前定义变量 set
set @crank=0;
select q.sid, total, @crank := @crank +1 as rank from(
SELECT sid,sum(score) AS total FROM sc GROUP BY sid ORDER BY sum(score) DESC)q;

# 方法二:在sql语句中定义变量
select q.sid, total, @crank := @crank +1 as rank from
	(SELECT @crank := 0) p,
	(SELECT sid,sum(score) AS total FROM sc GROUP BY sid ORDER BY sum(score) DESC) q;
+------+-------+------+
| sid  | total | rank |
+------+-------+------+
| 01   | 269.0 |    1 |
| 03   | 240.0 |    2 |
| 02   | 210.0 |    3 |
| 06   | 187.0 |    4 |
| 07   | 187.0 |    5 |
| 05   | 163.0 |    6 |
| 04   | 100.0 |    7 |
+------+-------+------+

对比三个结果,自己品 ~ :
在这里插入图片描述

参考地址:不理解了,看看这个文章

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

首先了解一下:CONCAT(),SUM(IF…)

CONCAT(): 用于将多个字符串连接成一个字符串,详情请看这里~

SELECT CONCAT(’My’, ‘S’, ‘QL’);     # 结果:'MySQL'
SELECT CONCAT(’My’, NULL, ‘QL’);    # 结果:NULL
SELECT CONCAT(14.3);				# 结果:'14.3'

SUM ( IF( ) ): IF(expr1,expr2,expr3),如果expr1的值为true,则返回expr2的值,如果expr1的值为false,则返回expr3的值。

  • sum (column ) : 把一列的值全部相加
  • sum( if ( 条件 , 1 , 0 ) ) : 满足“条件”,加1,不满足条件,加0
# 方法一
# 答案
SELECT cid,COUNT(sid) FROM sc GROUP BY cid;	# 根据课程分组,看人数

SELECT c.cname,p.count_people,t.* 
FROM course AS c 
LEFT JOIN 
(SELECT sc.cid,
CONCAT(sum(CASE WHEN sc.score>=85 AND sc.score<=100 THEN 1 ELSE 0 END )/COUNT(*)*100,'%') AS '[85-100]',
CONCAT(sum(CASE WHEN sc.score>=70 AND sc.score<85 THEN 1 ELSE 0 END )/COUNT(*)*100,'%') AS '[70-85)',
CONCAT(sum(CASE WHEN sc.score>=60 AND sc.score<70 THEN 1 ELSE 0 END )/COUNT(*)*100,'%') AS '[60-70)',
CONCAT(sum(CASE WHEN sc.score>=0 AND sc.score<60 THEN 1 ELSE 0 END )/COUNT(*)*100,'%') AS '[0-60)' FROM sc GROUP BY sc.cid) AS t 
ON c.cid=t.cid 
LEFT JOIN (SELECT cid,COUNT(sid) AS count_people FROM sc GROUP BY cid) AS p 
ON p.cid=t.cid;

+--------+--------------+------+----------+----------+----------+----------+
| cname  | count_people | cid  | [85-100] | [70-85)  | [60-70)  | [0-60)   |
+--------+--------------+------+----------+----------+----------+----------+
| 语文   |            6 | 01   | 0.0000%  | 66.6667% | 0.0000%  | 33.3333% |
| 数学   |            6 | 02   | 50.0000% | 16.6667% | 16.6667% | 16.6667% |
| 英语   |            6 | 03   | 33.3333% | 33.3333% | 0.0000%  | 33.3333% |
+--------+--------------+------+----------+----------+----------+----------+

# 方法二:
# 解析:首先查出各分段,成绩的百分比
SELECT cid,
	SUM(IF(sc.score >= 85,1,0)) / COUNT(1) AS '100-85',
	SUM(IF(sc.score >= 70 AND score < 85,1,0)) / COUNT(1) AS '85-70',
	SUM(IF(sc.score >= 60 AND score < 70,1,0)) / COUNT(1) AS '70-60',
	SUM(IF(sc.score < 60,1,0)) / COUNT(1) AS '60-0'
FROM sc
GROUP BY cid;
+------+--------+--------+--------+--------+
| cid  | 100-85 | 85-70  | 70-60  | 60-0   |
+------+--------+--------+--------+--------+
| 01   | 0.0000 | 0.6667 | 0.0000 | 0.3333 |
| 02   | 0.5000 | 0.1667 | 0.1667 | 0.1667 |
| 03   | 0.3333 | 0.3333 | 0.0000 | 0.3333 |
+------+--------+--------+--------+--------+


# 然后再通过关联表,找到课程的名字
SELECT sc.cid,c.cname,
		SUM(IF(sc.score >= 85,1,0)) / COUNT(1) AS '100-85',
		SUM(IF(sc.score >= 70 AND sc.score < 85,1,0)) / COUNT(1) AS '85-70',
		SUM(IF(sc.score >= 60 AND sc.score < 70,1,0)) / COUNT(1) AS '70-60',
		SUM(IF(sc.score < 60,1,0)) / COUNT(1) AS '60-0'
FROM sc,course c
WHERE sc.cid=c.cid
GROUP BY sc.cid,c.cname;

+------+--------+--------+--------+--------+--------+
| cid  | cname  | 100-85 | 85-70  | 70-60  | 60-0   |
+------+--------+--------+--------+--------+--------+
| 01   | 语文   | 0.0000 | 0.6667 | 0.0000 | 0.3333 |
| 02   | 数学   | 0.5000 | 0.1667 | 0.1667 | 0.1667 |
| 03   | 英语   | 0.3333 | 0.3333 | 0.0000 | 0.3333 |
+------+--------+--------+--------+--------+--------+

# 最后再关联课程所学人数
SELECT t.*,p.count_people FROM
(SELECT sc.cid,c.cname,
		SUM(IF(sc.score >= 85,1,0)) / COUNT(1) AS '100-85',
		SUM(IF(sc.score >= 70 AND sc.score < 85,1,0)) / COUNT(1) AS '85-70',
		SUM(IF(sc.score >= 60 AND sc.score < 70,1,0)) / COUNT(1) AS '70-60',
		SUM(IF(sc.score < 60,1,0)) / COUNT(1) AS '60-0'
FROM sc,course c
WHERE sc.cid=c.cid
GROUP BY sc.cid,c.cname) AS t
LEFT JOIN (SELECT cid,COUNT(sid) AS count_people FROM sc GROUP BY cid) AS p
ON t.cid=p.cid;

+------+--------+--------+--------+--------+--------+--------------+
| cid  | cname  | 100-85 | 85-70  | 70-60  | 60-0   | count_people |
+------+--------+--------+--------+--------+--------+--------------+
| 01   | 语文   | 0.0000 | 0.6667 | 0.0000 | 0.3333 |            6 |
| 02   | 数学   | 0.5000 | 0.1667 | 0.1667 | 0.1667 |            6 |
| 03   | 英语   | 0.3333 | 0.3333 | 0.0000 | 0.3333 |            6 |
+------+--------+--------+--------+--------+--------+--------------+

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

# 答案
SELECT *
FROM sc  
WHERE (SELECT COUNT(*) FROM sc AS a WHERE sc.cid =a.cid AND sc.score < a.score ) < 3
ORDER BY CId ASC,sc.score DESC;
+------+------+-------+
| sid  | cid  | score |
+------+------+-------+
| 01   | 01   |  80.0 |
| 03   | 01   |  80.0 |
| 05   | 01   |  76.0 |
| 01   | 02   |  90.0 |
| 07   | 02   |  89.0 |
| 05   | 02   |  87.0 |
| 01   | 03   |  99.0 |
| 07   | 03   |  98.0 |
| 02   | 03   |  80.0 |
| 03   | 03   |  80.0 |
+------+------+-------+

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

做了这么久,终于有一个简单点的了 😂

# 答案:
SELECT cid,COUNT(sid) FROM sc GROUP BY cid;
+------+------------+
| cid  | COUNT(sid) |
+------+------------+
| 01   |          6 |
| 02   |          6 |
| 03   |          6 |
+------+------------+

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

又一个简单点的了 休息休息 ~ ~ 😂

SELECT s.sname,s.sid 
FROM student s, (SELECT sid,COUNT(cid) FROM sc GROUP BY sid HAVING COUNT(cid) = 2) p
WHERE s.sid=p.sid;

+--------+------+
| sname  | sid  |
+--------+------+
| 周梅   | 05   |
| 吴兰   | 06   |
| 郑竹   | 07   |
+--------+------+
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页