The usage of MySQL having

Having words allows us to filter the various data after the group, where the words are filtered before aggregation, that is, before group by and having a sentence. The HAVING clause filters the group records after aggregation. My understanding is that there is no such data in the real table, and that data is surviving through some functions.

SQL instance:

First, show the total population and area of each area.
SELECT region, sum (population), sum (area) from the BBC GROUP by region

The return record is divided into groups with region first, which is the literal meaning of group by. After you finish the group, then use the aggregate function for each group
The different fields (one or more records) for the operation.

Second, show the total population and area of each area. Only those areas with more than 1000000 area are shown.

SELECT region, sum (population), sum (area)
From BBC
GROUP by region
Have SUM (area) >1000000

Here, we cannot use where to filter more than 1000000 of the area, because there is no such record in the table.
Instead, having clauses allows us to filter groups of data

MySQL determines the length of a field:

Select Home_Page from AAA table where Char_length (Trim (home_page)) <10 and Char_length (Trim (home_page)) >1;

The difference between the where and having clauses in MySQL
The WHERE and having clauses in MySQL can be used to filter records, but there are some differences in their usage, see example:
By combining GROUP BY and HAVING clauses to isolate records that are not duplicated, SQL is as follows:
Select Uid,email,count () as CT from edm_user081217 GROUP by email
And look at this, it's easy to understand.
Select Uid,email,count () as CT from edm_user081217 GROUP by email have ct > 1
Use GROUP by to group email first, in the have to filter more than 1, so find out is a duplicate record.

Here are the differences between having and where:
Select city from weather WHERE Temp_lo = (SELECT max (Temp_lo) from weather);
The objects that are acting differently. A WHERE clause acts on a table and a view, and a HAVING clause acts on a group.
Where the input row is selected before grouping and aggregation calculations (so that it controls which rows go into the aggregation calculation), and having the group selects the grouped rows after grouping and aggregating. Therefore, the WHERE clause cannot contain aggregate functions, because it makes no sense to attempt to use aggregate functions to determine which row input to the aggregation operation. Instead, the HAVING clause always contains a clustered function. (Strictly speaking, you can write a HAVING clause that doesn't use aggregation, but it's just a waste of effort.) The same conditions can be used more effectively in the WHERE phase. ）
In the previous example, we can apply the city name restriction in the where, because it does not need to be clustered. This is more efficient than increasing the limit in the having, because we avoid grouping and aggregating calculations for those rows that are not checked by where.
Sum up:
Having generally follows the group by, performing part of the record group selection to work.
Where is the execution of all data to work.
In addition, you can use aggregate functions such as having sum (qty) >1000

2017-07-05 12:03:48 Update:

Let's compare.

SELECT Ip,ip1,ip2,ip3,ip4,count () Ct,mobile_info lmaster_log from
GROUP by IP1,IP2,IP3 ORDER by IP1,IP2,IP3,IP4 has ct > 2;
SELECT Ip,ip1,ip2,ip3,ip4,count () Ct,mobile_info lmaster_log from
GROUP by IP1,IP2,IP3 have ct > 2 ORDER by IP1,IP2,IP3,IP4;
A statement

[ERR] 1064-you has an error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use near ' have ct > 2 ' at line 2

Statement 2 is the result, so when we use order BY, we should note that his position having clause must precede the order by after group by.

The usage of MySQL having