MySQL GROUP by using the having statement mate query

Source: Internet
Author: User


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.


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

    • 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.