Mysql query (note 2)

 

 

 

2. Aggregate functions must be used

 

 

For example, a table

The average score is calculated using an aggregate function.

 

3. grouping, sorting, filtering again, and several execution orders

4. query as required in the preceding table

Method 1: Step-by-Step Query

First, find out

Check the average score of these students.

The table is displayed.

Name

Zhang San

Li Si

 

Note the SQL Execution Sequence

Run

Expected table 2 is displayed:

Name

Zhang San

Zhang San

Li Si

Li Si

Wang Wu

 

Group and filter again

Note that although before group by, group by serves

Then in the table

Step 2:

After these students are found, the Student name is filtered again based on the most query condition. Because the pass score is filtered out in the first step, the original data is damaged, so a screening is required.

 

Select name, avg (score) from stu where name in (select name from stu where

Score <60 group by name having count (name)> = 2) group by name;

 

Analysis: First Run

See table 3:

Name	Score <60
Zhang San	0
Zhang San	1
Zhang San	1
Li Si	1
Li Si	1
Wang Wu	1

In

For example, field

From table 3:

Therefore

 

Gk> = 2;

Aggregate functions are executed simultaneously.

Result table:

Obviously, the second-to-second method is more concise and efficient.

To be continued ....!