The mysql maximum value query statement is: first group the stuname field; then use the MAX function to calculate the maximum value in each group. The code is [SELECT a.stuname,MAX(a. score) AS..].
【Related learning recommendations:mysql tutorial(Video)】
Mysql maximum value query statement is:
1, method 1
SELECT a.stuname,MAX(a.score) AS score FROM stuscore a GROUP BY a.`stuname` ;
In this statement, we perform the query on the stuname field Group the values into groups, and then use the MAX() function to calculate the maximum value in each group.
2. Method 2: Use connection
SELECT a.stuname,a.score AS score FROM stuscore a JOIN stuscore b ON a.`stuname`=b.`stuname` GROUP BY a.`score` HAVING a.`score`=MAX(b.`score`);
In the second sql statement, we use stuname as the judgment condition to connect the two tables. If we only execute
SELECT a.stuname,a.score AS score FROM stuscore a JOIN
stuscore b ON a.stuname=b.stunamewe will get the following result set:
Therefore, you will get this result set because during the connection, each record in the left table will be matched with each record in the right table according to the connection conditionON a.stuname=b.stuname". This is why duplicate records appear. This is a bit like a "double loop" in a programming language.
Then add grouping and grouping conditionsGROUP BY a.score HAVING a.score=MAX (b.score), you can get the maximum score of each student;
In fact,GROUP BY a.`score ` HAVING a.` score`=MAX(b.`score`)I didn’t quite understand this grouping condition at first, that is to say, I grouped according to the score field, but why can the MAX() function here calculate each The maximum score among students. We know that the first method is easier to understand, which is to group the names of each student, and then use theMAX()function to calculate the maximum value. This is for sure, but The second method is to group the score field. Here I think the reason should be that when connecting the two tables, the connection conditiona.stuname=b.stunamehas already been based on the student name Grouping is done, soMAX(b.score)can calculate the maximum score of each student.
If you want to know more about programming learning, please stay tunedphptrainingColumn!
The above is the detailed content of What is the maximum value statement in mysql query?. For more information, please follow other related articles on the PHP Chinese website!