【Mysql学习之旅-2】经典sql面试题及答案分析

云深i不知处

发布于 2020-09-16 10:11:44

9410

发布于 2020-09-16 10:11:44

文章被收录于专栏：测试基础测试基础测试基础

前言

在学习了Mysql的基础知识后，我们用一套sql练习题来实战一下。

关于练习所需要的数据，让我们继续玩坏倚天屠龙的江湖。

1、学生表 student（s_id：学生id，s_name：学生姓名，s_birth：学生生日，s_sex：学生性别）：

2、教师表teacher（t_id：教师id，t_name：教师姓名)

3、课程表 course（c_id：课程id，c_name：课程名称，t_id：教师id）：

4、成绩表 score（s_id：学生id，c_id：课程id，score：分数）

初始化数据的sql附在文章末尾

题目开始，先上几道硬菜，磕到牙齿的可以移步进阶题目和基础题目区压压惊。

挑战

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

--方法1：
SELECT 
	s_name,a.s_id,a.c_id,a.score 
FROM 
	score a 
LEFT JOIN 
	score b 
ON 
	a.c_id = b.c_id AND a.score<b.score
JOIN 
	student
ON
	a.s_id=student.s_id
GROUP BY 
	a.s_id,a.c_id,a.score HAVING COUNT(b.s_id)<3
ORDER BY 
	a.c_id,a.score DESC

--方法2：
SELECT 
	a.s_id,a.c_id,a.score 
FROM 
	score a
WHERE 
	(SELECT COUNT(1) FROM score b WHERE b.c_id=a.c_id 
	AND b.score>=a.score)<=2 ORDER BY a.c_id

简评：非常难以理解的一道题目，即使想透彻了也不容易记住，堪称劝退型难题。面试中，偶尔会考察。难度：⭐⭐⭐⭐⭐ 易考率：⭐⭐ 思路： 1、SELECT * FROM score a JOIN score b ON a.c_id=b.c_id WHERE a.c_id=‘03’ 这一句，形成了03课程的所有学员的两两组合（带上03，是为了简化数据，便于分析）

2、SELECT * FROM score a JOIN score b ON a.c_id=b.c_id WHERE a.c_id=‘03’ AND a.score<b.score又筛选出每个学员03课程比其他人低的情况（某个学员分越低，记录越多，没记录说明是最高分）

3、将这些记录按s_id分组，筛选出记录数小于3的（说明最多有两个人比自己分数高），即是前3名

2、查询“01”课程比“02”课程成绩高的所有学生的学号

SELECT 
	*
FROM 
	(SELECT s_id,score,c_id FROM score WHERE c_id='01') a
JOIN
	(SELECT s_id,score,c_id FROM score WHERE c_id='02') b
ON 
	a.s_id=b.s_id 
WHERE 
	a.score>b.score

简评：不容易想到，但答案相对第1题来说，就好理解多了。难度：⭐⭐⭐⭐ 频率：⭐⭐ 思路：通过SELECT s_id,score,c_id FROM score WHERE c_id='01’和SELECT s_id,score,c_id FROM score WHERE c_id='02’的两个临时表联查，得到每个学员的01、02课程成绩情况：

接下来，a.score>b.score条件直接筛选就可以了。

3、查询和"04"号的同学学习的课程完全相同的其他同学的信息

SELECT 
	score.s_id,COUNT(DISTINCT c_id) 
FROM 
	student JOIN score ON student.s_id=score.s_id 
GROUP BY 
	score.s_id 
HAVING COUNT(DISTINCT c_id)= 
	(SELECT COUNT(c_id) FROM score WHERE s_id='04') 
AND 
	s_id 
NOT IN
	-- 筛选出学过（04号同学未学课程）的学生，通过not in排除掉
	(SELECT s_id FROM score WHERE c_id IN
	(SELECT DISTINCT(c_id) FROM course WHERE c_id 
	NOT IN
	(SELECT c_id FROM score WHERE s_id='04'))) 
AND score.s_id!='04'

简评：难在思路的迂回性。面试中，很多有难度的sql也大多难在直接去考虑，难以获得答案。难度：⭐⭐⭐⭐ 频率：⭐⭐ 思路：通常思路，我们先获取04号同学的课程，再遍历其他同学，以此筛选。这种方式适用于编程，单纯的sql实现不了，我们应该从下面这个思路去考虑------>我和04号同学学习的课程数目一样多，且04号同学没学过的我也没学过，那么我不就是和04号同学学习的课程一样吗？

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

SELECT 
	a.s_id,b.s_name,
	MAX(CASE a.c_id WHEN '01' THEN a.score END ) 内功, 
	MAX(CASE a.c_id WHEN '02' THEN a.score END ) 剑法, 
	MAX(CASE a.c_id WHEN '03' THEN a.score END ) 拳法, 
	AVG(a.score) 
FROM 
	score a 
JOIN 
	student b ON a.s_id=b.s_id 
GROUP BY a.s_id ORDER BY AVG(a.score) DESC

简评：思路不难，关键在于理解透彻case when语法。难度：⭐⭐⭐ 频率：⭐⭐

SELECT a.c_id,b.c_name,MAX(score),MIN(score),ROUND(AVG(score),2),
	ROUND(100*(SUM(case when a.score>=60 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 及格率,
	ROUND(100*(SUM(case when a.score>=70 and a.score<=80 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 中等率,
	ROUND(100*(SUM(case when a.score>=80 and a.score<=90 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 优良率,
	ROUND(100*(SUM(case when a.score>=90 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 优秀率
FROM 
	score a 
LEFT JOIN 
	course b 
ON a.c_id = b.c_id GROUP BY a.c_id,b.c_name

简评：在case when语法的基础上，进一步掌握sql语句中进行逻辑运算的技巧难度：★★★☆ 频率：★★★

5、按各科成绩进行排序，并显示排名

SELECT a.s_id,a.c_id,
	@i:=@i + 1 AS 排名,
    @j:=(CASE WHEN @score=a.score THEN @j ELSE @i END) AS 并排排名,
    @score:=a.score AS score
FROM 
	(SELECT s_id,c_id,score FROM score GROUP BY s_id,c_id ORDER BY score DESC) a,
	(SELECT @i:= 0,@j:= 0,@score:= 0) s

简评：Mysql中没有rank函数，只能使用伪列的概念去实现排名算法。难度：★★★★ 频率：★★ 思路： 1、SELECT s_id,c_id,score FROM score GROUP BY s_id,c_id ORDER BY score DESC先进行了排序 2、(SELECT @i:=0,@j:=0,@score:=0) s对@i、@j、@score进行了初始化，初始值都为0 3、@i:=@i + 1，遍历每一行，@i逐行自增 4、@j:=(CASE WHEN @score=a.score THEN @j ELSE @i END)，遍历每一行，逻辑判断，@score与上一行的score相等则@j=@i，否则则自增一次

6、查询学生的总成绩并进行排名

SELECT a.s_id,
	@i:=@i+1 as i,
	@j:=(CASE WHEN @score=a.sum_score THEN @j ELSE @i END) AS rank,
	@score:=a.sum_score AS score
FROM 
	(SELECT s_id,SUM(score) AS sum_score FROM score GROUP BY s_id ORDER BY sum_score DESC)a,
	(SELECT @i:=0,@j:=0,@score:=0) s

简评：与上一题基本差不多，不同的是a这个临时表的数据内容而已。难度：★★★★ 频率：★★

7、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT 
	student.*,c.rank,c.score,c.c_id 
FROM 
	(SELECT a.s_id,a.score,a.c_id,@i:=@i+1 as rank from score a,(SELECT @i:=0)s 
	WHERE a.c_id='01' ORDER BY a.score DESC) c
LEFT JOIN 
	student ON c.s_id=student.s_id
WHERE rank BETWEEN 2 AND 3
UNION ALL 
SELECT 
	student.*,c.rank,c.score,c.c_id 
FROM 
	(SELECT a.s_id,a.score,a.c_id,@j:=@j+1 as rank from score a,(SELECT @j:=0)s 
	WHERE a.c_id='02' ORDER BY a.score DESC) c
LEFT JOIN 
	student ON c.s_id=student.s_id
WHERE 
	rank BETWEEN 2 AND 3
UNION ALL 
SELECT 
	student.*,c.rank,c.score,c.c_id 
FROM 
	(SELECT a.s_id,a.score,a.c_id,@k:=@k+1 as rank from score a,(SELECT @k:=0)s 
	WHERE a.c_id='03'  ORDER BY a.score DESC) c
LEFT JOIN 
	student ON c.s_id=student.s_id
WHERE rank BETWEEN 2 AND 3

简评：sql很长，不要被吓到，其实是3个相同的部分（where条件不同）通过UNION ALL合并而已。另外，筛选2、3名也是在排名的基础上进一步筛选。难度：★★★★☆ 频率：★★ 思路： 1、先将01课程的所有人的分数排序，再进一步根据rank BETWEEN 2 AND 3筛选出2、3名 2、使用同样方法，筛选02、03课程数据，使用 UNION ALL合并查询结果

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

SELECT 
	DISTINCT e.c_name,a.c_id,b.`80-100`,b.百分比,c.`60-80`,c.百分比,d.`0-60`,d.百分比 
FROM 
	score a
LEFT JOIN 
	(SELECT c_id,SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 END) AS `80-100`,
	ROUND(100*(SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 end)/count(*)),2)
	AS 百分比 FROM score GROUP BY c_id)b ON a.c_id=b.c_id
LEFT JOIN 
	(SELECT c_id,SUM(CASE WHEN score >60 AND score <=80 THEN 1 ELSE 0 END) AS `60-80`,
	ROUND(100*(SUM(CASE WHEN score >60 AND score <=80 THEN 1 ELSE 0 end)/count(*)),2) 
	AS 百分比 FROM score GROUP BY c_id)c ON a.c_id=c.c_id
LEFT JOIN 
	(SELECT c_id,SUM(CASE WHEN score >=0 AND score <=60 THEN 1 ELSE 0 END) AS `0-60`,
	ROUND(100*(SUM(CASE WHEN score >=0 AND score <=60 THEN 1 ELSE 0 END)/count(*)),2) 
	AS 百分比 FROM score GROUP BY c_id)d ON a.c_id=d.c_id
LEFT JOIN 
	course e ON a.c_id = e.c_id

简评：CASE WHEN和sql语句内进行逻辑运算的又一次应用。难度：★★★★ 频率：★★ 思路：SELECT c_id,SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 END) AS80-100, ROUND(100*(SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 END)/count(*)),2) AS 百分比 FROM score GROUP BY c_id得到所有课程80-100分成绩的统计临时表：

然后再用同样的方式，得到0-60、60-80的统计临时表，三表联查。

9、查询学生平均成绩及其名次

SELECT a.s_id,
	@i:=@i+1 as '不保留空缺排名',
	@k:=(CASE WHEN @avg_score=a.avg_s THEN @k ELSE @i END) AS '保留空缺排名',
	@avg_score:=avg_s AS '平均分'
FROM 
	(SELECT s_id,ROUND(AVG(score),2) AS avg_s FROM 
	score GROUP BY s_id ORDER BY avg_s DESC)a,
	(SELECT @avg_score:=0,@i:=0,@k:=0)b;

简评：与第5题类似，没什么特别需要说的。难度：★★★★ 频率：★★

10、查询各学生的年龄

SELECT 
	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
FROM student;

简评：本身的实现思路并不难，DATE_FORMAT掌握其用法即可。

11、分别查询本周、下周、本月、下月过生日的学生

--本周
SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)
--下周
SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 = WEEK(s_birth)
--本月
SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) =MONTH(s_birth)
--下月
SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =MONTH(s_birth)

进阶

1、查询及格学生（每门课程的分数>=60）的学生姓名

SELECT s_name FROM 
	student 
WHERE s_id NOT IN
	(SELECT DISTINCT(s_id) FROM score WHERE score<60)

或者：

SELECT s_name FROM 
	student a JOIN score b ON a.s_id=b.s_id 
GROUP BY b.s_id HAVING MIN(score)>60

2、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)

SELECT 
	s_name,ROUND(avg(score),2) AS avg 
FROM 
	student 
JOIN 
	score ON student.s_id=score.s_id 
GROUP BY 
	score.s_id HAVING avg<60 
UNION ALL 
SELECT 
	s_name,0 AS avg 
FROM 
	student 
WHERE s_id NOT IN(SELECT DISTINCT(s_id) FROM score)

-- 方法2
SELECT 
	s_name,IFNULL(ROUND(avg(score),2),0) AS avg 
FROM 
	student 
LEFT JOIN 
	score ON student.s_id=score.s_id 
GROUP BY score.s_id HAVING avg<60 OR avg is NULL

3、查询学过"张三丰"老师授课的同学的信息

SELECT 
	* 
FROM 
	score JOIN student ON score.s_id=student.s_id 
WHERE c_id IN
	(SELECT c_id FROM course c JOIN teacher t ON c.t_id=t.t_id WHERE t_name='张三丰')

4、查询没学过"张三丰"老师授课的同学的信息

SELECT 
	* 
FROM 
	student 
WHERE s_id NOT IN
	(SELECT s_id FROM score WHERE c_id 
IN
    (SELECT c_id FROM course c JOIN teacher t ON c.t_id=t.t_id WHERE t_name='张三丰'))

5、查询没有学全所有课程的同学的信息

SELECT 
	s_name,COUNT(c_id) AS count 
FROM 
	student 
LEFT JOIN 
	score ON student.s_id=score.s_id 
GROUP BY 
	student.s_id HAVING count<(SELECT COUNT(1) FROM course);

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

SELECT * FROM 
	student 
WHERE s_id IN
	(SELECT DISTINCT a.s_id FROM score a WHERE a.c_id 
	IN
	(SELECT a.c_id FROM score a WHERE a.s_id='01'));

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

SELECT 
	a.s_id,a.s_name,ROUND(AVG(b.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 score<60 GROUP BY s_id HAVING count(1)>=2)
GROUP BY a.s_id,a.s_name

8、查询所有学生的课程及分数情况；

SELECT 
	a.s_id,a.s_name,
	SUM(CASE c.c_name WHEN '内功' THEN b.score ELSE 0 END) AS '内功',
	SUM(CASE c.c_name WHEN '剑法' THEN b.score ELSE 0 END) AS '剑法',
	SUM(CASE c.c_name WHEN '拳法' THEN b.score ELSE 0 END) AS '拳法',
	SUM(b.score) as  '总分'
FROM 
	student a 
LEFT JOIN 
	score b ON a.s_id = b.s_id 
LEFT JOIN 
	course c ON b.c_id = c.c_id 
GROUP BY a.s_id,a.s_name

9、查询选修"张三丰"老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT 
	s_id,MAX(score) 
FROM 
	score 
WHERE 
	c_id 
IN
	(SELECT c_id FROM teacher JOIN course 
	ON teacher.t_id=course.t_id WHERE t_name='张三丰')

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

SELECT * FROM score a,score b where a.c_id != b.c_id and a.score = b.score

11、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT 
	c_id,COUNT(*) AS total 
FROM 
	score 
GROUP BY c_id HAVING total>4 ORDER BY total DESC,c_id ASC

12、查询选修了全部课程的学生信息

SELECT 
	* FROM student WHERE s_id 
IN
	(SELECT s_id FROM score GROUP BY s_id HAVING COUNT(*)=(SELECT COUNT(*) FROM course))

基础

1、查询不及格的课程

SELECT 
	a.s_id,a.c_id,b.c_name,a.score 
FROM 
	score a 
LEFT JOIN 
	course b ON a.c_id = b.c_id
WHERE a.score<60

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

SELECT 
	a.s_id,b.s_name 
FROM 
	score a 
LEFT JOIN 
	student b ON a.s_id = b.s_id
WHERE a.c_id = '01'	AND a.score>80

3、查询每个同学的姓名、选课数、总成绩

SELECT 
	s_name,COUNT(c_id),SUM(score) 
FROM 
	student a 
LEFT JOIN 
	score b ON a.s_id=b.s_id 
GROUP BY b.s_id

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

SELECT 
	s_name,ROUND(avg(score),2) AS avg 
FROM 
	student 
JOIN 
	score ON student.s_id=score.s_id 
GROUP BY score.s_id HAVING avg>60

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

SELECT student.s_name,COUNT(c_id),IFNULL(SUM(score),0) AS sum FROM student LEFT JOIN score ON student.s_id=score.s_id GROUP BY student.s_id ORDER BY sum DESC

6、查询"张"姓老师的数量

SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE '张%'

7、查询"01"课程分数小于60，按分数降序排列的学生信息

SELECT 
	a.*,b.c_id,b.score
FROM 
	student a,score b
WHERE 
	a.s_id=b.s_id 
AND
	b.c_id='01' AND b.score<60
ORDER BY
	b.score DESC;

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

SELECT 
	a.s_name,b.c_name,c.score 
FROM 
	course b 
LEFT JOIN 
	score c ON b.c_id = c.c_id
LEFT JOIN 
	student a ON a.s_id=c.s_id 
WHERE c.score>=70

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT a.* 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';

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT a.* FROM 
	student a 
WHERE 
	a.s_id 
IN 
	(SELECT s_id FROM score WHERE c_id='01' ) 
AND 
	a.s_id NOT IN(SELECT s_id FROM score WHERE c_id='02')

11、查询男生、女生人数

SELECT s_sex,COUNT(s_sex) FROM student GROUP BY s_sex

12、查询名字中含有"圆"字的学生信息

SELECT * FROM student WHERE s_name LIKE '%圆%';

13、查询同名同性学生名单，并统计同名人数

SELECT 
	a.s_name,a.s_sex,count(*) 
FROM 
	student a JOIN student b 
ON 
	a.s_id !=b.s_id and a.s_name = b.s_name 
AND 
	a.s_sex = b.s_sex
GROUP BY a.s_name,a.s_sex

14、查询1990年出生的学生名单

SELECT s_name FROM student WHERE s_birth LIKE '1337%'

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

SELECT 
	c_id,ROUND(AVG(score),2) AS avg_score 
FROM 
	score 
GROUP BY c_id ORDER BY avg_score DESC,c_id ASC

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

SELECT 
	a.s_id,b.s_name,ROUND(avg(a.score),2) AS avg 
FROM 
	score a
LEFT JOIN 
	student b on a.s_id=b.s_id 
GROUP BY s_id HAVING avg>=85

17、查询课程名称为"内功"，且分数低于60的学生姓名和分数

SELECT 
	a.s_name,b.score 
FROM 
	score b 
JOIN 
	student a ON a.s_id=b.s_id 
WHERE 
	b.c_id=(SELECT c_id FROM course WHERE c_name ='内功') 
AND b.score<60

18、求每门课程的学生人数

SELECT c_id,count(*) FROM score GROUP BY c_id;

19、检索至少选修两门课程的学生学号

SELECT s_id,count(*) AS count FROM score GROUP BY s_id HAVING count>=2

数据初始化

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `s_id` varchar(20) NOT NULL,
  `s_name` varchar(20) NOT NULL DEFAULT '',
  `s_birth` varchar(20) NOT NULL DEFAULT '',
  `s_sex` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '张无忌', '1337-06-17', '男');
INSERT INTO `student` VALUES ('02', '宋青书', '1333-12-21', '男');
INSERT INTO `student` VALUES ('03', '丁敏君', '1336-06-21', '女');
INSERT INTO `student` VALUES ('04', '周芷若', '1340-09-26', '女');
INSERT INTO `student` VALUES ('05', '圆真', '1320-12-01', '男');
INSERT INTO `student` VALUES ('06', '圆觉', '1316-03-01', '男');
INSERT INTO `student` VALUES ('07', '殷梨亭', '1310-07-01', '男');
INSERT INTO `student` VALUES ('08', '纪晓芙', '1310-01-20', '女');


-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c_id` varchar(20) NOT NULL,
  `c_name` varchar(20) NOT NULL DEFAULT '',
  `t_id` varchar(20) NOT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '内功', '03');
INSERT INTO `course` VALUES ('02', '剑法', '02');
INSERT INTO `course` VALUES ('03', '拳法', '01');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `t_id` varchar(20) NOT NULL,
  `t_name` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三丰');
INSERT INTO `teacher` VALUES ('02', '灭绝师太');
INSERT INTO `teacher` VALUES ('03', '空见');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `s_id` varchar(20) NOT NULL,
  `c_id` varchar(20) NOT NULL,
  `score` int(3) DEFAULT NULL,
  PRIMARY KEY (`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('01', '01', '99');
INSERT INTO `score` VALUES ('01', '02', '96');
INSERT INTO `score` VALUES ('01', '03', '90');
INSERT INTO `score` VALUES ('02', '01', '59');
INSERT INTO `score` VALUES ('02', '02', '75');
INSERT INTO `score` VALUES ('02', '03', '68');
INSERT INTO `score` VALUES ('03', '01', '55');
INSERT INTO `score` VALUES ('03', '02', '50');
INSERT INTO `score` VALUES ('03', '03', '60');
INSERT INTO `score` VALUES ('04', '01', '77');
INSERT INTO `score` VALUES ('04', '02', '88');
INSERT INTO `score` VALUES ('05', '01', '88');
INSERT INTO `score` VALUES ('05', '02', '90');
INSERT INTO `score` VALUES ('06', '01', '86');
INSERT INTO `score` VALUES ('06', '03', '99');
INSERT INTO `score` VALUES ('07', '02', '58');
INSERT INTO `score` VALUES ('07', '03', '98');

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2020-06-25 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql