Having a sentence allows us to filter the various data after the group
the use of 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.
SQL instance:
first, show the total population and area of each region
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 the group is finished, the different fields (one or more records) in each group are calculated with an aggregate function.
second, show the total population and area of each area. Show only those areas with more than 1000000 area
SELECT region, sum (population), sum (area) from Bbcgroup by regionhaving 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)) 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 E-mail, in the have to filter more than 1, so find out is the duplicate records. The following are
the difference 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 rows that are not checked by where. The 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
How to filter a group record with a clause in MySQL