MySQL GROUP by using the having statement mate query

Attention:

Having statements must be used in conjunction with group by, which can only be group, but not only having a having, of course, and this can happen at the same time.

The difference between having a phrase and where!!!
The WHERE clause acts on the base table or view from which you select a tuple that satisfies the criteria. The having phrase acts on the group from which the group that satisfies the condition is selected.

Here's an example of how

First we create such a table:

A brief introduction to the meaning of each field: job-jobs, sal-– wages, dep-– Department

Insert data:

Requirements: From this table to filter out the job is not "M", the department to divide the average wage more than 28000, in descending order of records.

Code:

SELECT  DEP,    JOB,    AVG(SAL)FROM    EMPLWHERE   JOB <> ‘M‘GROUP BY DEP, JOBHAVING AVG(SAL) > 28000ORDER BY 3 DESC;

• 1
• 2
• 3
• 4
• 5
• 6

Let's take a step-by-step analysis of this long SQL statement:

First, from getting all the records in the table, that is:

Second, where to get

In the third step, group by Dep,job gets

Fourth step, having AVG (SAL) >28000
That is, from the resulting five groups to find the average wage greater than 28000 of the group, that is, 第二、四、五 group

The fifth step is descending order: (Of course, select we have been using, in fact, this query with the whole sentence has six clauses) so the end result is

One notable point here is what happens when we change the third field of the query result to Sal.

SELECT    DEP,    JOB,    SALFROM    EMPLWHERE    JOB <> ‘M‘GROUP BY DEP, JOBHAVING AVG(SAL) > 28000ORDER BY 3 DESC;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15

So the result is the first record of Sal in each group, which is of no practical significance, because it is neither the most wage nor the minimum wage, he is just the first record in each group corresponding to the SAL

As I said above, 31000 is blu The first record of this group is sal,33000 is the first record of the GRE group SAL,32000 is the first record of the red Group

Look at the results of the query:

So when we are projecting, we must pay special attention to the actual meaning of each domain!

Be sure to remember this rule

The SELECT clause contains set functions

Columns included in the SELECT clause:
1. Columns in Set functions
2. Columns that are not in the SET function-these columns must all be included in the GROUP BY clause.

(that is, only grouping attributes and set functions appear in the column Name list of the SELECT clause after using the GROUP BY clause)

MySQL GROUP by using the having statement mate query