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 ITEM3FROM Student S inner join Student S1 on S.name = S1.name and S.course <> S1.courseinner join Student S2 on S.name = S2.name and S.course <> S2.courseWHERE 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 existsSELECT 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.nameFROM Student SGROUP BY S.nameHaving MIN(S.score)>=80