SQL 练习编写能力（1） （GROUP BY HAVING）

1. 用一条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