1-MySQL - SQL强化41题
before
最开始是41题，随着整理，貌似不止41题了，whatever，干就完了。
或者牛客网：https://www.nowcoder.com/exam/oj?tab=SQL篇&topicId=199&fromPut=pc_wzcpa_wxcarl_sf，由易到难跟着系列走
学生选课
准备数据：school.sql
-- mysql -uroot -p --default-character-set=utf8 <school.sql
DROP DATABASE IF EXISTS school;
CREATE DATABASE school CHARSET utf8;
USE school;
SET AUTOCOMMIT=0;
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '1', '87'),('53', '13', '3', '88'),('54', '13', '4', '89');
COMMIT;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
开始吧！
查询大于60分的成绩
SELECT
num
FROM
score
WHERE num > 60 ORDER BY num DESC;
查询每门课程及对应的老师姓名
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher
ON course.cid = teacher.tid;
查询学生及学生所在的班级
SELECT
student.sname,
class.caption
FROM
student
LEFT JOIN class
ON student.class_id = class.cid;
查询每个班级及每个班级学生人数和数量
SELECT
class.caption,
COUNT(student.sname),
GROUP_CONCAT(student.sname)
FROM
student
LEFT JOIN class
ON student.class_id = class.cid
GROUP BY class.caption;
查询男生和女生的个数及对应的学生姓名
SELECT
gender,
COUNT(gender),
GROUP_CONCAT(sname)
FROM
student
GROUP BY gender;
查询每个学生所学的课程名称
SELECT
student.sname,
GROUP_CONCAT(course.cname)
FROM
student
RIGHT JOIN score
ON student.sid = score.student_id
LEFT JOIN course
ON score.course_id = course.cid
GROUP BY sname;
查询每个同学每门课的成绩
SELECT
student.sname,
course.cname,
score.num
FROM
student
LEFT JOIN score
ON student.sid = score.sid
LEFT JOIN course
ON score.course_id = course.cid;
查询平均成绩大于60分的同学的学号(sid)和平均成绩
-- 有两种解题思路，第一种就是先联表，在过滤条件
SELECT
student.sname,
AVG(score.num) AS '平均成绩'
FROM
student
LEFT JOIN score
ON student.sid = score.student_id
GROUP BY student.sname
HAVING AVG(score.num) > 60;
-- 第二种就是先使用子查询，再联表，各有优势吧
SELECT
student.sname,
tmp.avg_num
FROM
student
RIGHT JOIN
(SELECT
student_id,
AVG(num) AS avg_num
FROM
score
GROUP BY student_id
HAVING AVG(num) > 60) AS tmp
ON student.sid = tmp.student_id
ORDER BY tmp.avg_num;
查询所有同学的学号(sid)，姓名，选课数和总成绩
SELECT
student.sid,
student.sname,
COUNT(score.course_id) AS '选课数',
SUM(score.num) AS '总成绩'
FROM
student
RIGHT JOIN score
ON student.sid = score.student_id
GROUP BY score.student_id;
查询姓 "李" 的老师的个数
SELECT
COUNT(1)
FROM
teacher
WHERE tname LIKE '李%';
查询没有选择 "李平老师" 课程的学生姓名和学号(sid)
-- 解题思路是:先看 "李平老师" 教授了哪些课程,并且拿到课程 id
SELECT
teacher.tname,
course.cname,
course.cid
FROM
teacher
LEFT JOIN course
ON teacher.tid = course.teacher_id
WHERE teacher.tname = '李平老师';
-- 根据课程 id ，去 score 表过滤出学习了 "李平老师" 课程的学生的 sid
SELECT
student_id
FROM
score
WHERE course_id IN
(SELECT
course.cid
FROM
teacher
LEFT JOIN course
ON teacher.tid = course.teacher_id
WHERE teacher.tname = '李平老师')
GROUP BY student_id; -- 因为有人或者学生有重名的，所以分组过滤以下
-- 有了学生的sid，就可以去 student 表中 not in 一下就行了
SELECT
sid,
sname
FROM
student
WHERE sid NOT IN
(SELECT
student_id
FROM
score
WHERE course_id IN
(SELECT
course.cid
FROM
teacher
LEFT JOIN course
ON teacher.tid = course.teacher_id
WHERE teacher.tname = '李平老师')
GROUP BY student_id);
查询 "生物" 课程比 "物理" 课程成绩高的所有学生的姓名
-- 方法一，解题思路是：先分别获取 "生物" 和 "物理" 课程的成绩
SELECT
score.student_id,
course.cname,
score.num
FROM
course
LEFT JOIN score
ON course.cid = score.course_id
WHERE course.cname = "生物";
SELECT
score.student_id,
course.cname,
score.num
FROM
course
LEFT JOIN score
ON course.cid = score.course_id
WHERE course.cname = "物理";
-- 再拼成一个 student_id 生物 生物成绩 物理 物理成绩 这样的一张表,再根据成绩过滤，此时的表已经有了符合条件的sid
SELECT
A.student_id AS sid
FROM
(SELECT
score.student_id,
course.cname,
score.num
FROM
course
LEFT JOIN score
ON course.cid = score.course_id
WHERE course.cname = "生物") AS A
INNER JOIN
(SELECT
score.student_id,
course.cname,
score.num
FROM
course
LEFT JOIN score
ON course.cid = score.course_id
WHERE course.cname = "物理") AS B
ON A.student_id = B.student_id
WHERE A.num > B.num;
-- 根据 sid 再去联 学生表 取出 学生姓名
SELECT
student.sid,
student.sname,
C.biology,
C.biology_score,
C.physics,
C.physics_score
FROM
student
INNER JOIN
(SELECT
A.student_id AS sid,
A.cname AS biology,
A.num AS biology_score,
B.cname AS physics,
B.num AS physics_score -- 为了结果好看，多提取了几个字段
FROM
(SELECT
score.student_id,
course.cname,
score.num
FROM
course
LEFT JOIN score
ON course.cid = score.course_id
WHERE course.cname = "生物") AS A
INNER JOIN
(SELECT
score.student_id,
course.cname,
score.num
FROM
course
LEFT JOIN score
ON course.cid = score.course_id
WHERE course.cname = "物理") AS B
ON A.student_id = B.student_id
WHERE A.num > B.num) AS C
ON student.sid = C.sid;
-- 方法二，思路是，先单独从course表中过滤物理和生物课程的id
-- 再从score表中根据课程id过滤出来学习过这两门课的学生，并且进行联表(联表的目的是便于比较成绩)使用where过滤
-- 此时有了符合条件的记录，然后再与学生表联接，得到最终的学生名称
SELECT
student.sid,
student.sname,
C.biology_score,
C.physics_score
FROM
student
INNER JOIN
(SELECT
A.student_id,
A.num AS biology_score,
B.num AS physics_score
FROM
(SELECT
student_id,
num
FROM
score
WHERE course_id IN
(SELECT
course.cid
FROM
course
WHERE cname = "生物")) AS A
INNER JOIN
(SELECT
student_id,
num
FROM
score
WHERE course_id IN
(SELECT
course.cid
FROM
course
WHERE cname = "物理")) AS B
ON A.student_id = B.student_id
WHERE A.num > B.num) AS C
ON student.sid = C.student_id;
查询学过 "001" and "002" 课程的同学的姓名
-- 方法1
SELECT
student.sid,
student.sname
FROM
student
INNER JOIN score
ON student.sid = score.student_id
WHERE score.course_id = 1
OR score.course_id = 2
GROUP BY score.student_id
HAVING COUNT(score.course_id) > 1;
-- 方法2，使用union all 优化 or 语句
SELECT
student.sid,
student.sname
FROM
student
INNER JOIN
(SELECT
student_id
FROM
score
WHERE course_id = 1
UNION
ALL
SELECT
student_id
FROM
score
WHERE course_id = 2) AS A
ON student.sid = A.student_id
GROUP BY A.student_id
HAVING COUNT(A.student_id) > 1;
查询学过 "李平老师" 教授的所有课程的学生姓名
SELECT
student.sid,
student.sname
FROM
student
RIGHT JOIN
(-- 这里一定要用右联接，左联接的话会将不符合条件的学生也返回
SELECT
student_id
FROM
score
WHERE course_id IN
(SELECT
cid
FROM
course
LEFT JOIN teacher
ON course.teacher_id = teacher.tid
WHERE teacher.tname = "李平老师")
GROUP BY student_id
HAVING COUNT(course_id) =
(SELECT
COUNT(cid)
FROM
course
LEFT JOIN teacher
ON course.teacher_id = teacher.tid
WHERE teacher.tname = "李平老师")) AS A
ON student.sid = A.student_id;
查询课程编号 "002" 的学生成绩比课程编号 "001" 的学生成绩低的同学的姓名
-- 法1
SELECT
student.sid,
student.sname,
C.one,
C.two
FROM
student
RIGHT JOIN
(SELECT
A.student_id,
A.num AS ONE,
B.num AS two
FROM
(SELECT
*
FROM
score
WHERE course_id IN
(SELECT
cid
FROM
course
WHERE cid = 1)) AS A
INNER JOIN
(SELECT
*
FROM
score
WHERE course_id IN
(SELECT
cid
FROM
course
WHERE cid = 2)) AS B
ON A.student_id = B.student_id) AS C
ON student.sid = C.student_id
WHERE ONE > two;
-- 法2
SELECT
sid,
sname
FROM
student
WHERE sid IN
(SELECT
t1.student_id
FROM
(SELECT
num num2,
student_id
FROM
score
WHERE course_id = 2) t2
INNER JOIN
(SELECT
student_id,
num num1
FROM
score
WHERE course_id = 1) t1
ON t1.student_id = t2.student_id
WHERE num2 < num1);
查询成绩小于60分的学生姓名和成绩
SELECT
student.sid,
student.sname
FROM
student
WHERE sid IN -- 再 in 的时候，就去重了，也可以使用分组或者distinct去重
(SELECT
student_id -- 因为有多门成绩，所以，学生id是有重复的
FROM
score
WHERE num < 60);
-- 使用分组
SELECT
student.sid,
student.sname
FROM
student
WHERE sid IN
(SELECT
student_id
FROM
score
WHERE num < 60
GROUP BY student_id);
-- 使用distinct和联表
SELECT
student.sid,
student.sname
FROM
student
RIGHT JOIN
(SELECT DISTINCT
student_id -- 但是 distinct 效率不高
FROM
score
WHERE num < 60) AS A
ON student.sid = A.student_id
查询没有选择学习全部课程的学生的姓名
-- 法1 使用 in
SELECT
sid,
sname
FROM student WHERE sid IN
(SELECT
student_id
FROM
score
GROUP BY student_id
HAVING COUNT(student_id) !=
(SELECT
COUNT(cid)
FROM
course));
-- 法2，使用 连表
SELECT
student.sid,
student.sname
FROM
student
RIGHT JOIN
(SELECT
student_id
FROM
score
GROUP BY student_id
HAVING COUNT(student_id) !=
(SELECT
COUNT(cid)
FROM
course)) AS A
ON student.sid=A.student_id;
查询至少有一门课是和学生 "001" 选择的课程是一样的学生姓名
-- 1. 先要知道 001 这个学生所有学过的课程 id
SELECT
course_id
FROM
score
WHERE student_id = 1;
-- 2. 找学生001以外的学生学过的课程，只要有一门和学生001重合就是我们想要的结果
SELECT
*
FROM
student
WHERE sid IN -- 使用 in 其实子查询中可以不用分组的
(SELECT
student_id
FROM
score
WHERE student_id != 1
AND course_id IN
(SELECT
course_id
FROM
score
WHERE student_id = 1)
GROUP BY student_id);
查询和学生 "003" 选择的课程完全一样的学生姓名
--------------- 法1 ----------------
-- 思路是先知道学生 003 选择了哪些课程，并且，为了方便，将 003 选择的所有课程使用 group_concat 进行拼接
-- 后续的判断对比拼接后字符串，算是一种取巧的行为
SELECT student_id,GROUP_CONCAT(course_id) FROM score WHERE student_id=3;
+------------+-------------------------+
| student_id | GROUP_CONCAT(course_id) |
+------------+-------------------------+
| 3 | 1,2,3,4 |
+------------+-------------------------+
1 row in set (0.00 sec)
-- 然后，同样使用 group_concat 拼接出来其他同学的课程id，和 003 的拼接字符串对比
SELECT
student.sid,
student.sname,
A.course_list
FROM
student
INNER JOIN
(SELECT
student_id,
GROUP_CONCAT(course_id) AS course_list
FROM
score
WHERE student_id != 3 -- 先把 003 这个家伙排除出去
GROUP BY student_id
HAVING GROUP_CONCAT(course_id) =
(SELECT
GROUP_CONCAT(course_id)
FROM
score
WHERE student_id = 3)) AS A
ON student.sid = A.student_id;
+-----+--------+-------------+
| sid | sname | course_list |
+-----+--------+-------------+
| 4 | 张一 | 1,2,3,4 |
| 5 | 张二 | 1,2,3,4 |
| 6 | 张四 | 1,2,3,4 |
| 7 | 铁锤 | 1,2,3,4 |
| 8 | 李三 | 1,2,3,4 |
| 9 | 李一 | 1,2,3,4 |
| 10 | 李二 | 1,2,3,4 |
| 11 | 李四 | 1,2,3,4 |
| 12 | 如花 | 1,2,3,4 |
+-----+--------+-------------+
9 rows in set (0.00 sec)
--------------- 法2 ----------------
-- 首先拿到 003 学生 学过的课程 id 结果得到 1 2 3 4
SELECT * FROM score WHERE student_id = 3;
SELECT COUNT(1) FROM score WHERE student_id = 3; -- 得到结果 4 门课
-- 排除 001 学生,但其他学生选择的课程还需要处理
SELECT student_id FROM score WHERE student_id != 3 GROUP BY student_id; -- 排除 003 后的其他学生，因为课程有门，学生id由重复，这里使用分组去重
-- 过滤出来和 001 学生选课门数一样的学生,每个同学的选课数量必须和 001 选择的课程数量相等
SELECT student_id FROM score WHERE student_id != 3 GROUP BY student_id HAVING COUNT(1) = (SELECT COUNT(1) FROM score WHERE student_id = 3);
-- 虽然选课数量相等，但选择的课程可能不一样，所以再次根据课程id去判断
SELECT student_id FROM score WHERE student_id !=3 AND student_id IN (
SELECT student_id FROM score WHERE student_id != 1 GROUP BY student_id HAVING COUNT(1) = (SELECT COUNT(1) FROM score WHERE student_id = 3)
) AND course_id IN ( -- in 这一步判断的是保证其他学生和 003 选择的课程一样
SELECT course_id FROM score WHERE student_id = 3
) GROUP BY student_id HAVING COUNT(1) = (SELECT COUNT(1) FROM score WHERE student_id = 3);
-- 最后，再根据 student_id获取学生的姓名，这里用的in，也可以使用连表
SELECT sid,sname FROM student WHERE sid IN ( -- 获取学生姓名
SELECT student_id FROM score WHERE student_id !=3 AND student_id IN (
SELECT student_id FROM score WHERE student_id != 1 GROUP BY student_id HAVING COUNT(1) = (SELECT COUNT(1) FROM score WHERE student_id = 3)
) AND course_id IN ( -- in 这一步判断的是保证其他学生和 003 选择的课程一样
SELECT course_id FROM score WHERE student_id = 3
) GROUP BY student_id HAVING COUNT(1) = (SELECT COUNT(1) FROM score WHERE student_id = 3)
);
+-----+--------+
| sid | sname |
+-----+--------+
| 4 | 张一 |
| 5 | 张二 |
| 6 | 张四 |
| 7 | 铁锤 |
| 8 | 李三 |
| 9 | 李一 |
| 10 | 李二 |
| 11 | 李四 |
| 12 | 如花 |
+-----+--------+
9 rows in set (0.01 sec)
按平均成绩从高到低显示各科成绩，按如下形式显示： 学生ID、学生姓名、各科成绩(如：语文，英语，数学)、考了几科、平均分
SELECT
current_student.`sid`,
current_student.`sname`,
(SELECT num FROM score LEFT JOIN course ON score.`course_id`=course.`cid` WHERE course.`cname`='生物' AND score.`student_id`=current_student.`sid`) AS '生物',
(SELECT num FROM score LEFT JOIN course ON score.`course_id`=course.`cid` WHERE course.`cname`='物理' AND score.`student_id`=current_student.`sid`) AS '物理',
(SELECT num FROM score LEFT JOIN course ON score.`course_id`=course.`cid` WHERE course.`cname`='体育' AND score.`student_id`=current_student.`sid`) AS '体育',
(SELECT num FROM score LEFT JOIN course ON score.`course_id`=course.`cid` WHERE course.`cname`='美术' AND score.`student_id`=current_student.`sid`) AS '美术',
(SELECT COUNT(num) FROM score WHERE student_id=current_student.sid) AS '考了几科',
IF ((SELECT SUM(num)/4 FROM score WHERE student_id=current_student.sid GROUP BY student_id), (SELECT SUM(num)/4 FROM score WHERE student_id=current_student.sid GROUP BY student_id), 0) AS '平均分'
FROM student AS current_student LEFT JOIN score
ON score.`student_id`=current_student.`sid`
LEFT JOIN course
ON course.cid=score.course_id
GROUP BY current_student.sid
ORDER BY (SELECT SUM(num)/4 FROM score WHERE student_id=current_student.sid GROUP BY student_id) DESC;
查询各科成绩最高分和最低分：以如下形式显示：课程ID，最高分，最低分；如果最低分小于60，显式 "再接再励"
SELECT course_id,MAX(num),MIN(num) FROM score GROUP BY course_id;
SELECT
course_id,MAX(num) AS '最高分',
CASE WHEN MIN(num) < 60 THEN "再接再励" ELSE MIN(num) END AS '最低分'
FROM score
GROUP BY course_id;
删除 score 表 "李平老师" 相关记录
EXPLAIN DELETE FROM score WHERE course_id IN (
SELECT cid FROM course WHERE teacher_id IN (
SELECT tid FROM teacher WHERE tname='李平老师'));
查询与学号为"2"的同学选课程完全相同的其他 学生学号和姓名
-- 首先知道学号为"2"的同学的选课是什么，好跟后面的SQL语句做对比。
SELECT
student.sid,
student.sname,
GROUP_CONCAT(course.cname)
FROM score
LEFT JOIN course ON score.course_id=course.cid
LEFT JOIN student ON score.student_id=student.sid
WHERE student.sid=2
GROUP BY student.sid;
+------+-------+----------------------------+
| sid | sname | GROUP_CONCAT(course.cname) |
+------+-------+----------------------------+
| 2 | 钢蛋 | 生物,体育,美术 |
+------+-------+----------------------------+
-- 然后再写SQL
SELECT
student.sid,
student.sname,
GROUP_CONCAT(course.cname)
FROM score
LEFT JOIN course ON score.course_id=course.cid
LEFT JOIN student ON score.student_id=student.sid
WHERE
score.course_id in (SELECT course_id FROM score WHERE student_id=2)
AND score.student_id in (SELECT student_id from score WHERE student_id != 2 GROUP BY student_id HAVING COUNT(1)=(SELECT COUNT(1) FROM score WHERE student_id=2))
GROUP BY
score.student_id
HAVING
COUNT(1)=(SELECT COUNT(1) FROM score WHERE student_id=2);
+------+-------+----------------------------+
| sid | sname | GROUP_CONCAT(course.cname) |
+------+-------+----------------------------+
| 13 | 刘三 | 体育,生物,美术 |
+------+-------+----------------------------+
that's all,see also:
