Home >Database >Mysql Tutorial >What is the maximum value statement in mysql query?

What is the maximum value statement in mysql query?

coldplay.xixi
coldplay.xixiOriginal
2020-09-03 13:33:586222browse

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..].

What is the maximum value statement in mysql query?

【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:

What is the maximum value statement in mysql query?

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;

What is the maximum value statement in mysql query?

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Related articles

See more