MySQL: Query where having and use of statistical functions

Question:

The table is as follows:

Use only one SELECT statementQuery: average score (average score of all subjects) for students whose failing subjects are greater than or equal to 2 ).

 
Mysql> select * from student; + ------ + --------- + -------- + | Name | subject | score | + ------ + --------- + -------- + | Zhang San | mathematics | 80 | Zhang San | language | 53 | Zhang San | English | 59 | Li Si | mathematics | 55 | Li Si | Chinese | 56 | Li Si | English | 50 | Wang Wu | mathematics | 100 | Wang Wu | Chinese | 90 | + ------ + --------- + -------- + 8 rows in Set
 

Key points:

1. count the number of students that cannot meet the requirements of the subject.

2. Calculate the average score.

Misunderstanding 1: Use count for statistics. Count is always the count of all rows!

Positive Solution: Use sum for statistics. Calculate the average score of all users before filtering.

 
Mysql> select name, AVG (score), sum (score <60) as CNT from student group by name having CNT> = 2; + ------ + ------------ + ----- + | Name | AVG (score) | CNT | + ------ + ------------ + ----- + | Zhang San | 64.0000 | 2 | Li Si | 53.6667 | 3 | + ------ + ------------ + ----- + 2 rows in Set
 

Sum (score <60) indicates the number of different subjects.

Having is used for filtering. Where is not used here. Where can only be used for existing columns.

Of course, you can also use the left link of the subquery, Which is troublesome.

Question 2: The table above.

Query the maximum score of each student.

Misunderstanding 1:

 
Mysql> select *, max (score) from student group by name; + ------ + --------- + ------- + ------------ + | Name | subject | score | max (score) | + ------ + --------- + ------- + ------------ + | Zhang San | mathematics | 80 | 80 | Li Si | mathematics | 55 | 56 | Wang Wu | mathematics | 100 | 100 | + ------ + --------- + ------- + ------------ + 3 rows in Set
 

Although the maximum score is found, the displayed subject is incorrect. Because group by always takes the first record.

Positive Solution:

1. Where subquery (first find the maximum score of each student and refresh the Record Based on the score ).

 
Mysql> select * from student where score in (select max (score) from student group by name ); + ------ + --------- + ------- + | Name | subject | score | + ------ + --------- + ------- + | Zhang San | mathematics | 80 | Li Si | language | 56 | Wang Wu | mathematics | 100 | + ------ + --------- + ------- + 3 rows in Set
 

2. From subquery (first sort and get the first record in group by, that is, the maximum score)

 
Mysql> select * from (select * from student order by score DESC) as TMP group by name; + ------ + --------- + ------- + | Name | subject | score | + ------ + --------- + ------- + | Zhang San | mathematics | 80 | Li Si | language | 56 | Wang Wu | mathematics | 100 | + ------ + --------- + ------- + 3 rows in Set
 

The two methods are very different. The second method can only retrieve one record, if the maximum score is the same.