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

name   kecheng    fenshu 

张三     语文     81

张三     数学     75

李四     语文     76

李四     数学     90

王五     语文     81

王五     数学     100

王五     英语     90

 

方法一：

 

　　思路：如果能获得一张表，由学生姓名，语文成绩，数学成绩，英语成绩的表，剩下的就是在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 

 

 

　　易错点：内表的 score字段必须要取别名，否则会报错 。

 

 

 

　　方法二：

 

　　思路：采用逆向思维想想。。。。。。求三门成绩都大于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