怎样把不嵌套mysql语言改成嵌套的

Question

CREATE TABLE S (
sno INT NOT NULL PRIMARY KEY,
sn CHAR(20) NOT NULL,
sex CHAR(8),
dep INT
);

CREATE TABLE C (
cno INT NOT NULL,
cn CHAR(20),
tno INT NOT NULL,
credit INT,
PRIMARY KEY(cno,tno)
);

CREATE TABLE SC (
sno INT NOT NULL,
cno INT NOT NULL,
g INT,
PRIMARY KEY(sno,cno)
);

CREATE TABLE Teacher (
tno INT NOT NULL PRIMARY KEY,
tn CHAR(8) NOT NULL,
dep INT
);

CREATE TABLE Dept (
dep INT NOT NULL PRIMARY KEY,
dn CHAR(20) NOT NULL
);

ALTER TABLE S ADD age SMALLINT;
ALTER TABLE S MODIFY age INT;

INSERT INTO S VALUES
(1001,'喵喵','m',10,20),
(1002,'汪汪','f',10,21),
(1003,'咩咩','m',10,21),
(1004,'哞哞','f',20,21),
(1005,'呱呱','m',20,22),
(1006,'嘎嘎','f',20,22),
(1007,'咕咕哒','f',30,20),
(1008,'西西','m',30,20),
(1009,'哈哈','f',40,21),
(1010,'张三','m',50,19),
(1011,'李四','f',30,20),
(1012,'王五', 'm',50,20),
(1013,'马六','f',40,18),
(1014,'伏七','m',20,20),
(1015,'邢八','f',10,20),
(1016,'刘九','m',20,21),
(1017,'邹十','f',30,22),
(1018,'吕十一','m',40,19),
(1019,'吴十二','f',50,18),
(1020,'陈十三','m',50,19),
(1021,'姚十四','m',30,19);

INSERT INTO C VALUES
(1,'数据结构',101,4),
(2,'数据库',102,4),
(3,'离散数学',103,4),
(4,'C语言程序设计',104,2),
(5,'高等量子力学',105,3),
(6,'高等数学',106,6),
(7,'线性代数',107,4),
(8,'管理统计学',108,4),
(9,'财务管理学',109,3),
(10,'商务智能',100,4);

INSERT INTO SC VALUES
(1001,1,80),
(1001,2,85),
(1001,3,78),
(1002,1,72),
(1002,2,82),
(1002,3,86),
(1003,1,92),
(1003,3,90),
(1004,1,87),
(1004,4,90),
(1005,1,85),
(1005,4,92),
(1006,5,99),
(1006,2,100),
(1007,1,80),
(1007,3,91),
(1008,7,82),
(1008,3,59),
(1009,10,73),
(1009,9,78),
(1010,1,92),
(1010,3,87),
(1011,10,85),
(1011,6,88),
(1012,6,90),
(1012,8,76),
(1013,10,59),
(1013,7,95),
(1014,6,79),
(1014,10,97),
(1015,8,66),
(1015,3,70),
(1016,4,69),
(1016,5,85),
(1017,10,97),
(1017,9,96),
(1018,7,83),
(1018,5,86),
(1019,4,90),
(1019,10,78),
(1020,9,84),
(1020,1,99),
(1021,3,77),
(1021,7,88);
INSERT INTO Teacher VALUES
(101,'张小天',10),
(102,'胡小伟',10),
(103,'黄程',10),
(104,'郭冰',20),
(105,'钱祺',30),
(106,'邢文',20),
(107,'王笑',50),
(108,'周思',40),
(109,'庞锦',50),
(100,'李想',30);

INSERT INTO Dept VALUES
(10,'计算机'),
(20,'经管'),
(30,'物理'),
(40,'广告'),
(50,'音录');

SELECT S.sn,MAX(g)
FROM S,SC
WHERE S.sno = SC.sno
GROUP BY cno;

如果用嵌套结构
SELECT S.sn,MAX(g)
FROM S,C
WHERE S.sno IN(
          SELECT SC.sno
          FROM SC
          WHERE SC.cno=C.cno)
GROUP BY C.cno

![img](https://img-mid.csdnimg.cn/release/static/image/mid/ask/122304252666135.png "#left")

写出来的就是错的，该怎么改成正确的嵌套结构

Answer 1

SELECT S.sn,MAX(g)
FROM S,C

还是用了关联查询，而且没加上on