先取值,后查询
select * from
(select num,student_id FROM score where score.course_id IN (SELECT course.cid from course where course.cname='物理')) as A
LEFT join
(select num,student_id FROM score where score.course_id IN (SELECT course.cid from course where course.cname='生物')as W) AS B
on B.student_id=A.student_id;
# 命名错误------ Unknown column 'A.student_id' in 'field list'
select * from
(select num,student_id FROM score where score.course_id IN (SELECT course.cid from course where course.cname='物理')) as A
LEFT join
(select num,student_id FROM score where score.course_id IN (SELECT course.cid from course where course.cname='生物')as W) AS B
on B.student_id=A.student_id;
# 命名错误------ Unknown column 'A.student_id' in 'field list'
(select num,student_id FROM score a where a.course_id IN (SELECT cid from course b where b.cname='物理')) as A
LEFT join
(select num,student_id FROM score a where a.course_id IN (SELECT cid from course b where b.cname='生物')) AS B
on B.student_id=A.student_id;你试试这个
(SELECT num,student_id FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.cname='物理')) A
LEFT JOIN
(SELECT num,student_id FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.cname='生物')) B
ON B.student_id=A.student_id;
1. as A处, A字母使用的是全解输入
2. AS B处, 在AS B处前面的小括号里面,已经有一个AS W了
语句嵌套的有点多,建议使用以下形式:
select A.*
FROM score AS A
LEFT JOIN course AS AC ON A.course_id = AC.cid
LEFT JOIN score AS B ON A.student_id = B.student_id
LEFT JOIN course AS BC ON B.course_id = BC.cid
WHERE AC.cname = N'物理' AND BC.cname = N'生物'