What is the maximum value statement in mysql query?

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.stuname we 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 condition ON 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 the MAX() 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 condition a.stuname=b.stuname has already been based on the student name Grouping is done, so MAX(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!