检索所学课程包含学生S3所学课程的学生学号

问题描述

书上的解法感觉很反正常人思维。自己写了一个。

分别有表如下：
教师关系   T (T#,TNAME,TITLE)
课程关系   C(C#,CNAME,T#)
学生关系   S (S#,SNAME,AGE,SEX)
选课关系   SC(S#,C#,SCORE)

最新

原本写的是错的，工作了两年现在重新写一遍。
当时宿舍里想了一晚上，结果现在再想一下明明特别简单的一个问题。
只记得当时书上的sql逻辑感觉贼烂，隐隐约约感觉饶了一大圈。

这个迷一样复杂的字段名也是原因

而且这个题目名字也绕的要死，就该叫《查询选了S3同学选的课的学生》

-- 2.再查选的课里有（S3同学选的课程号）的学生学号
select distinct `S#` from sc
where sc.`C#` in(
	-- 1. 先查出S3同学选的所有课程号
	select 	`C#`  from sc
	where `S#` =1
)
1
2
3
4
5
6
7

更新：按qq_53143129 的说法 此处求的应该是选了S3同学选择的所有课的同学……说之前没想到，一说就感觉题意应该是这样。补一下这种写法。
以及这个字段名起的是真的恶心。

-- 2.查出其他选了S3同学所选课程的课程数
select sc.`S#`,count(DISTINCT `C#`) from sc
where sc.`C#` in(
	-- 1. 先查出S3同学选的所有课程号
	select 	`C#`  from sc
	where `S#` =1
)
group by sc.`S#`
-- 3.如果其他同学选的S3同学选的课程数=S3同学选的课程数,那么即该同学选了S3同学的所有课程
having count(DISTINCT `C#`) = (select count(distinct `C#`) from sc where `S#`=1)
1
2
3
4
5
6
7
8
9
10

创建表sql代码

// An highlighted block
/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 80020
 Source Host           : 127.0.0.1:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 80020
 File Encoding         : 65001

 Date: 18/10/2020 16:07:29
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for c
-- ----------------------------
DROP TABLE IF EXISTS `c`;
CREATE TABLE `c`  (
  `C#` int(0) NOT NULL,
  `CNAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `T#` int(0) NOT NULL,
  PRIMARY KEY (`C#`) USING BTREE,
  INDEX `T#`(`T#`) USING BTREE,
  CONSTRAINT `c_ibfk_1` FOREIGN KEY (`T#`) REFERENCES `t` (`T#`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of c
-- ----------------------------
INSERT INTO `c` VALUES (1, 'python', 1);
INSERT INTO `c` VALUES (2, 'C语言', 3);
INSERT INTO `c` VALUES (3, 'java设计模式', 4);

-- ----------------------------
-- Table structure for s
-- ----------------------------
DROP TABLE IF EXISTS `s`;
CREATE TABLE `s`  (
  `S#` int(0) NOT NULL,
  `SNAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `AGE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `SEX` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`S#`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of s
-- ----------------------------
INSERT INTO `s` VALUES (1, 'zjy', '20', '男');
INSERT INTO `s` VALUES (2, 'hzr', '20', '男');
INSERT INTO `s` VALUES (3, 'wzk', '20', '男');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `S#` int(0) NOT NULL,
  `C#` int(0) NOT NULL,
  `SCORE` int(0) NOT NULL,
  PRIMARY KEY (`S#`, `C#`) USING BTREE,
  INDEX `C#`(`C#`) USING BTREE,
  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`S#`) REFERENCES `s` (`S#`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`C#`) REFERENCES `c` (`C#`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES (1, 1, 100);
INSERT INTO `sc` VALUES (1, 2, 100);
INSERT INTO `sc` VALUES (1, 3, 100);
INSERT INTO `sc` VALUES (2, 2, 50);
INSERT INTO `sc` VALUES (3, 1, 50);

-- ----------------------------
-- Table structure for t
-- ----------------------------
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t`  (
  `T#` int(0) NOT NULL,
  `TNAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `TITLE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`T#`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t
-- ----------------------------
INSERT INTO `t` VALUES (1, 'llh', '讲师');
INSERT INTO `t` VALUES (3, 'wwt', '院长');
INSERT INTO `t` VALUES (4, 'sxd', '系主任');

SET FOREIGN_KEY_CHECKS = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102