Mysql: Query where having and use of statistical functions

Mysql pen Question 1: Query where having and the use of statistical functions questions: tables are as follows: www.2cto.com only use one select statement to query: fail subjects greater than or equal to 2 subjects, average score (average score of all subjects ). [SQL] <span style = "font-size: 18px;"> 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 </span> key points: www.2cto.com. 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. [SQL] <span style = "font-size: 18px;"> 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 </span> sum (score <60) is to count 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: [SQL] 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, however, the displayed subjects are 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 ). [SQL] 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 (sort first, obtain the first record with the maximum score in group by.) www.2cto.com [SQL] mysql> select * from (select * from student order by score desc) 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 type can only retrieve one record, if the maximum score is the same.