Question09-查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

问题分析

• 这个问题主要在于确定一个条件之后，怎么再指定一个条件，如果思维还仅仅停留在单表和基础的连接表操作，那么这题会显地比较难

• 不过要说简单也其实很简单，只需要学会对问题进行简单的拆分即可，如下所示

  • -- 学过01，且学过02
    
    -- 学过01
    SELECT sc.`SID` FROM SC sc WHERE sc.`CID` = '01';
    
    -- 学过02
    SELECT sc.`SID` FROM SC sc WHERE sc.`CID` = '02';
    
    -- 连起来
    SELECT stu.* FROM Student stu, SC sc
    WHERE stu.`SID` = sc.`SID` AND sc.`CID` = '01'
    AND sc.`SID` IN (
       SELECT sc.`SID` FROM SC sc WHERE sc.`CID` = '02'
    )
    
    

• 分析结束，接下来就是完善SQL

方法1

• where连接

  • SELECT stu.* FROM Student stu, SC sca
    WHERE
      stu.`SID` = sca.`SID` AND sca.`CID` = '01'
      AND stu.`SID` IN (
    	SELECT scb.`SID` FROM SC scb
    	WHERE
    	  scb.`SID` = sca.`SID` -- 虽然不指定也能查出来数据，但是还是指定比较符合题意
    	  AND scb.`CID` = '02'
      );
    

  • left join on连接

    • SELECT stu.* FROM Student stu
      LEFT JOIN SC sca ON stu.`SID` = sca.`SID` AND sca.`CID` = '01'
      WHERE stu.`SID` IN 
      (
        SELECT scb.`SID` FROM SC scb
        WHERE
          scb.`SID` = sca.`SID`
          AND scb.`CID` = '02'
      );
      
      

• 查询结果如下所示

  • 

方法2

• 左外连接

  • SELECT stu.* FROM Student stu
    LEFT JOIN SC sca ON stu.`SID` = sca.`SID` AND sca.`CID` = '01'
    WHERE EXISTS (
    	SELECT 1 FROM SC scb
    	WHERE
    	  scb.`SID` = sca.`SID`
    	  AND scb.`CID` = '02'
      );
    

  • 关于EXISTS关键字的具体用法可以参考以下博客

    • https://blog.csdn.net/zhangzehai2234/article/details/124652056

  • 这里使用select 1的原因是可以让搜索效率更快，其实select *和select 1的区别不大，在这里使用EXISTS关键字仅仅作为判断子查询是否有返回数据

  • 具体select 1的作用可以参考以下博客

    • https://blog.csdn.net/mywaya2333/article/details/122902059

• where做表连接

  • SELECT stu.* FROM Student stu, SC sca
    WHERE
      stu.`SID` = sca.`SID` AND sca.`CID` = '01'
      AND EXISTS (
    	SELECT 1 FROM SC scb
    	WHERE
    	  scb.`SID` = sca.`SID`
    	  AND scb.`CID` = '02'
      );
    

• 查询结果如下所示

  •