表STUDENTS (sid,sname,email,grade);
表TEACHERS(tid,tname,email,salary);
表COURSES(cid,cname,chour);
表CHOECES(cno,sid,tid,cid,score);要求:找出选修课程ERP成绩最高的学生编号SELECT sid
FROM CHOECES,COURSES
WHERE CHOECES.cid=COURSES.cid AND cname='ERP' AND score=(
SELECT MAX(score)
FROM CHOECES,COURSES
WHERE CHOECES.cid=COURSES.cid AND cname='ERP'
)这是我写的语句,可是感觉很冗长,应该有更好的写法
还望各位高手赐教
表TEACHERS(tid,tname,email,salary);
表COURSES(cid,cname,chour);
表CHOECES(cno,sid,tid,cid,score);要求:找出选修课程ERP成绩最高的学生编号SELECT sid
FROM CHOECES,COURSES
WHERE CHOECES.cid=COURSES.cid AND cname='ERP' AND score=(
SELECT MAX(score)
FROM CHOECES,COURSES
WHERE CHOECES.cid=COURSES.cid AND cname='ERP'
)这是我写的语句,可是感觉很冗长,应该有更好的写法
还望各位高手赐教
*
from
CHOECES a
join
COURSES b on a.cid=b.CID and b.cname='ERP'--改改
where
not exists(select 1 from CHOECES where cid=a.cid and score>a.score)
这边的1还有后面没有前缀的cid和score是什么,有什么作用?
谢谢
没有前缀是本身的cid 和score