SELECT *FROM student s , course c , scores sc
WHERE s.id=sc.S_id
AND c.id=sc.C_id
AND sc.C_id=1
AND EXISTS (
SELECT sc.S_id FROM scores as sc WHERE scores.S_id =sc.S_id sc.C_id='2'
)
为什么我用mysql： SELECT sc.S_id FROM scores as sc WHERE scores.S_id =sc.S_id sc.C_id='2'
报错: 报错码：1054
Unknown column 'scores.S_id' in 'where clause'
建表语句如下：
CREATE TABLE Student
(
id INT(3) PRIMARY KEY NOT NULL AUTO_INCREMENT,
Sname VARCHAR(20) NOT NULL,
Sage INT(3) NOT NULL,
Ssex VARCHAR(10) NOT NULL
)
CREATE TABLE Course
(
id INT(3) PRIMARY KEY NOT NULL AUTO_INCREMENT,
Cname VARCHAR(20) NOT NULL,
T_id INT(3) NOT NULL
);
CREATE TABLE Scores
(
S_id INT(3) NOT NULL ,
C_id INT(3) NOT NULL,
score INT(3) NOT NULL
);
CREATE TABLE Teacher
(
id INT(3) PRIMARY KEY NOT NULL AUTO_INCREMENT,
Tname VARCHAR(20) NOT NULL
);
------解决方案--------------------
SELECT sc.S_id FROM scores as sc WHERE sc.S_id =sc.S_id sc.C_id='2'
当在from中定义了别名之后，在整个SELECT语句中原表名不可再用，要全部换成别名
------解决方案--------------------
SELECT sc.S_id FROM scores as sc WHERE scores.S_id =sc.S_id and sc.C_id='2'
------解决方案--------------------
子查询有毛病:
SELECT S_id FROM scores WHERE scores.S_id =S_id and C_id='2'
子查询表的昵称不能和主查询表的昵称相同，否则不知道究竟是哪个表了．
------解决方案--------------------
你那个太混乱了，内外层的别名不能这样搞，人都看不懂哪个对应哪个，何况机器
SELECT *
FROM student s ,
course c ,
scores sc
WHERE s.id = sc.S_id
AND c.id = sc.C_id
AND sc.C_id = 1
AND EXISTS ( SELECT sc.S_id
FROM scores AS sc1
WHERE sc1.S_id = sc.S_id
AND sc1.C_id = '2' )
------解决方案--------------------
因为EXISTS是关联性子查询，需要关联外表才能一起执行，非关联性子查询就不用
------解决方案--------------------
试试这个
SELECT *
FROM student s ,
course c ,
scores sc
WHERE s.id = sc.S_id
AND c.id = sc.C_id
AND sc.C_id = 1
AND sc.s_id in ( SELECT sc1.S_id
FROM scores AS sc1
WHERE sc1.C_id = '2' )