"Turn" a SQL query out the names of students who have scored more than 80 of each course

Name Kecheng Fenshu
Zhang San language 81
Zhang San Mathematics 75
John Doe Language 76
John Doe Mathematics 90
Harry Language 81
Harry Mathematics 100
Harry English 90

Method One:

　　ideas: If you can get a table, by the student's name, language scores, math scores, English scores of the table, the rest is in the where conditions to filter and can get the desired results.

　　specific measures: through the self-linking method, with "name" as the connection condition, since the connection three times, you can get the data line containing the name and the results of the three courses. Although you can get the data columns you want. But there will be a lot of redundant repeating columns!

　　Comments: This method is based on the topic, according to the problem, the! However, multiple table connections are time-consuming. and SQL statements are more complex, there are a lot of things to notice.

　　

SELECT D.nameFrom(SELECT S.name,s.scoreAs Item1,s1.scoreAs Item2,s2.scoreAsITEM3FromStudent SInnerJoin Student S1On S.name= S1.nameand S.course<> S1.courseinner join Student S2 on s.name = S2.name and s.course <>where s.score>=80 and s1.score>=80 and s2.score>=80) D group by D.name  

　　Easy wrong point: the score field of the inner table must be alias, otherwise it will error.

Method Two:

　　Thinking : using reverse thinking to think about ...  To find that the results are more than 80 of the people, but also to find out that there is a score of less than 80 people, and then remove these people are not more than the number of people are more than 80? In the past, the mathematical logic of reverse thinking is really useful ah!!

　　Specific methods: first scan the table, find out the name of the person who has a score of less than 80, and then scan the table again, with not in or not EXISTS method.

　　Reviews: This approach uses reverse thinking to quickly write efficient and simple SQL statements.

NotInchSELECTDISTINCT A.nameFromStudent AWHERE A.nameNotInch(SELECTDistinct S.nameFrom Student SWHERE S.score<80)NotExistsSELECTDISTINCT A.namefrom Student A where Span style= "color: #808080;" >not exists (select 1 from Student S where s.score <80 and s.name =a.name) */            

Method Three:

SELECT s.namefrom Student Sby s.nameMIN (s.score)>=    

"Turn" a SQL query out the names of students who have scored more than 80 of each course