有一张成绩表SC,表结构为SC(sid,cid,course),分部对应是学生ID,课程ID和学生成绩,有如下测试数据
查询结果格式如下所示:sc
sid cid course
1 001 67
1 002 89
1 003 94
2 001 95
2 002 88
2 003 78
3 001 94
3 002 77
3 003 90
查询出'001'课程分数大于'002'课程分数的学生学号
预期结果为
sid
2
3
解决:
SELECT one.sid
FROM (
SELECT sid
,cid
,course
FROM sc AS x
WHERE x.cid = '001'
) AS one
INNER JOIN (
SELECT sid
,cid
,course
FROM sc AS x
WHERE x.cid = '002'
) AS two
ON one.sid = two.sid
WHERE one.course &g