一个SQL查询出每门课程的成绩都大于80的学生姓名

梦里梦外; 2022-07-14 14:48 322阅读 0赞

name kecheng fenshu  
张三 语文 81  
张三 数学 75  
李四 语文 76  
李四 数学 90  
王五 语文 81  
王五 数学 100  
王五 英语 90

方法一：

思路：采用逆向思维想想。。。。。。求三门成绩都大于80的人，也可以是使先查出有成绩小于80 的人，再除去这些人不就是三门成绩都大于80的人了么？ 以前学过的数学逻辑逆向思维还真是有用的阿！！

具体办法：先扫描表，查出有成绩小于80的人的姓名，然后再次扫描表，用not in 或not exists 方法。

点评：此方法采用逆向思维，能快速写出高效且简单的 SQL语句。

//not in 
    SELECT DISTINCT A.name FROM Student A WHERE A.name not in( SELECT Distinct S.name FROM Student S WHERE S.score <80) //not exists SELECT DISTINCT A.name From Student A where not exists (SELECT 1 From Student S Where S.score <80 AND S.name =A.name) /*exists 详解 取出 外表第一条数据 ，然后与内表 根据连接条件 ， 形成一条或多条数据，判断这些生成的数据中是否存在 或者是不存在符合where条件的 。结果为ture的那条外表 记录旧被查询出来！ 实例过程： 取出外表的第一条记录， 和内表通过姓名条件连接，这时候产生2两记录， 根据 not exists是判断不存在。 条件是 score<80 . 而这两条记录存在一条记录小于80，所以于not exists 不符合， 该条记录不被查出。 */

方法二：

SELECT S.name FROM Student S GROUP BY S.name Having MIN(S.score)>=80

方法三：

思路：如果能获得一张表，由学生姓名，语文成绩，数学成绩，英语成绩的表，剩下的就是在WHERE条件中筛选及可以获得想要的结果。

具体办法：通过自连接的办法，以“姓名”为连接条件，自连接三次，便可以获得包含又姓名和三门课程成绩的数据行。虽然可以得到想要的数据列。但会有很多冗余重复列！

点评：此方法是根据题目，依题解题，中规中矩！ 不过多张表连接非常耗费时间。而且SQL语句也比较复杂，需要注意事项很多。

SELECT  D.name  FROM (
    SELECT  S.name,S.score AS ITEM1,S1.score AS ITEM2,S2.score AS ITEM3
    FROM Student S 
    inner join Student S1 on S.name = S1.name and S.course <> S1.course
    inner join Student S2 on S.name = S2.name and S.course <> S2.course
    WHERE S.score>=80 and S1.score>=80 and S2.score>=80
    ) D 
    GROUP BY D.name

参考文章：[http://www.cnblogs.com/hongyan5682/p/4816444.html][http_www.cnblogs.com_hongyan5682_p_4816444.html]

[http_www.cnblogs.com_hongyan5682_p_4816444.html]: http://www.cnblogs.com/hongyan5682/p/4816444.html