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

最新推荐文章于 2024-08-06 21:34:07 发布

子敬c

最新推荐文章于 2024-08-06 21:34:07 发布

阅读量5.9k

点赞数 4

分类专栏： MySQL 文章标签： MySQL

本文链接：https://blog.csdn.net/weixin_40471400/article/details/100559266

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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

1、今天在一个可以练习MySQL的博客上发现一个SQL语句有问题，想优化一下。点击我去原文博客
添加学生表

CREATE TABLE `student`  (
  `SID` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Sage` datetime(0) NULL DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20 00:00:00', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06 00:00:00', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01 00:00:00', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01 00:00:00', '女');
INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20 00:00:00', '女');
INSERT INTO `student` VALUES ('09', '哦嗯', '2019-07-10 09:13:08', '男');
INSERT INTO `student` VALUES ('10', '小明', '2019-09-19 15:18:51', '男');

添加学生成绩表（表中cid代表课程）

CREATE TABLE `sc`  (
  `SID` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `CID` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `score` decimal(18, 1) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('01', '01', 80.0);
INSERT INTO `sc` VALUES ('01', '02', 90.0);
INSERT INTO `sc` VALUES ('01', '03', 99.0);
INSERT INTO `sc` VALUES ('02', '01', 70.0);
INSERT INTO `sc` VALUES ('02', '02', 50.0);
INSERT INTO `sc` VALUES ('02', '03', 80.0);
INSERT INTO `sc` VALUES ('03', '01', 80.0);
INSERT INTO `sc` VALUES ('03', '02', 80.0);
INSERT INTO `sc` VALUES ('03', '03', 80.0);
INSERT INTO `sc` VALUES ('04', '01', 50.0);
INSERT INTO `sc` VALUES ('04', '02', 30.0);
INSERT INTO `sc` VALUES ('04', '03', 20.0);
INSERT INTO `sc` VALUES ('05', '01', 76.0);
INSERT INTO `sc` VALUES ('05', '02', 87.0);
INSERT INTO `sc` VALUES ('06', '01', 31.0);
INSERT INTO `sc` VALUES ('06', '03', 34.0);
INSERT INTO `sc` VALUES ('07', '02', 89.0);
INSERT INTO `sc` VALUES ('07', '03', 98.0);
INSERT INTO `sc` VALUES ('08', '01', 76.0);
INSERT INTO `sc` VALUES ('08', '02', 87.0);
INSERT INTO `sc` VALUES ('10', '01', 85.0);

在原文的第十三个问题是查出来“查询和"01"号的同学学习的课程完全相同的其他同学的信息”，原文的SQL语句如下：

select Student.* from Student where SID in (select distinct SC.SID from SC where SID <> '01' and SC.CID in (select distinct CID from SC where SID = '01') 
group by SC.SID having count(1) = (select count(1) from SC where SID='01'))

当时想着他是否适合其他情况呢，比如用来“查询和"05"号的同学学习的课程完全相同的其他同学的信息”，经过测试是不行的。测试结果如下：
第一到四条都不是我们想要的数据。
在这里插入图片描述下面给出我的解决步骤;
1、查出05同学的课程号

select cid from sc where sid = '05'

2、找出跟05同学所学课程不一样的同学学号（逆向思维 not in ）

select a.sid from sc a where a.cid not in (select cid from sc where sid = '05')

3、在成绩表中找到跟05同学学习一样课程的同学的学号

select b.sid from sc b where b.sid not in (select a.sid from sc a where a.cid 
not in (select cid from sc where sid = '05')) and b.sid <> '05' group by b.sid 
having count(1)  = (select count(1) from sc where sid = '05')

ps: (1） count(1) 会统计表中的所有的记录数，包含字段为null 的记录。
（2） count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
本次题目中二者都可以的。
4、查出符合条件的学生的信息

select * from student where sid in (select b.sid from sc b where b.sid not in 
(select a.sid from sc a where a.cid not in (select cid from sc where sid = '05')) 
and b.sid <> '05' group by b.sid 
having count(1)  = (select count(1) from sc where sid = '05'))

此时的查询结果如下图：
在这里插入图片描述
将最终的查询语句换成“查询和"01"号的同学学习的课程完全相同的其他同学的信息”也是可以的，查询结果如下：

select * from student where sid in (select b.sid from sc b where b.sid 
not in (select a.sid from sc a where a.cid not in 
(select cid from sc where sid = '01')) and b.sid <> '01' group by b.sid 
having count(1)  = (select count(1) from sc where sid = '01'))

在这里插入图片描述
如有不足之处，请多多指教。