SQL面试50题-41

41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）

思路:把每个同学的每门课程作为一个字段进行比较

SELECT a.s_id,a.s_score "语文" ,b.s_score "数学",c.s_score "英语" 
FROM 
(SELECT * FROM Score WHERE c_id = '01' ) a
INNER JOIN 
(SELECT * FROM Score WHERE c_id = '02' ) b ON b.s_id = a.s_id
INNER JOIN 
(SELECT * FROM Score WHERE c_id = '03' ) c ON c.s_id = b.s_id
WHERE a.s_score=b.s_score AND a.s_score=c.s_score