1. -- 1
    2. -- 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
    3. SELECT * FROM (SELECT t1.SId,class1,class2 FROM
    4. (SELECT SId,score AS class1 FROM sc WHERE sc.CId = '01')AS t1,
    5. (SELECT SId,score AS class2 FROM sc WHERE sc.CId = '02')AS t2 
    6. WHERE t1.SId = t2.SId AND t1.class1 > t2.class2) r LEFT JOIN Student ON Student.SId = r.SId;
    8. -- 查询同时存在" 01 "课程和" 02 "课程的情况
    9. SELECT * FROM (SELECT * FROM sc WHERE sc.CId = '01') AS t1,(SELECT * FROM sc WHERE sc.CId = '02') AS t2
    10. WHERE t1.SId = t2.SId;
    12. -- 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
    13. SELECT * FROM (SELECT * FROM sc WHERE sc.CId = '01') AS t1 LEFT JOIN (SELECT * FROM sc WHERE sc.CId = '02') AS t2 ON t1.SId = t2.SId;
    15. -- 查询不存在" 01 "课程但存在" 02 "课程的情况
    16. SELECT * FROM sc WHERE sc.SId NOT IN (SELECT SId FROM sc WHERE sc.CId = '01') AND sc.CId = '02';