The having sentence in MySql provides instructions for filtering group records
- 2020-05-14 05:13:01
- OfStack
The use of the having
The having sentence allows us to filter the data into groups. The where sentence filters the records before aggregation, that is, before the group by and having sentences. The having clause filters the group records after aggregation.
SQL instances:
1. Show the total population and total area of each region
SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region
Divide the returned records into groups using region, which is the literal meaning of GROUP BY. After grouping, the aggregation function is then used to perform operations on different fields (1 or more records) in each group.
2. Show the total population and total area of each region. Only areas larger than 1 million are shown
SELECT region, SUM(population), SUM(area)FROM bbcGROUP BY regionHAVING SUM(area) > 1000000
Here, we cannot use where to filter more than 1,000,000 regions, because such a record does not exist in the table. In contrast, the having clause allows us to filter groups of data into groups
mysql determines the length of a field
select home_page from aaa where char_length(trim(home_page))1;
The difference between the where and having clauses in mysql
Both the where and having clauses in mysql can be used to filter records, but there are some differences in their usage. See example 1:
Use the group by and having clauses to find non-duplicating records. sql is as follows:
select uid,email,count(*) as ct from `edm_user081217` GROUP BY email
And then when you look at this, it makes sense
select uid,email,count(*) as ct from `edm_user081217` GROUP BY email HAVING ct > 1
First, group by is used to group email, and then having is used to filter the records greater than 1, so that duplicate records can be found
Differences between having and where:
Select city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
The object of action is different. The WHERE clause applies to tables and views, and the HAVING clause applies to groups. WHERE selects the input rows before grouping and aggregation calculations (thus, it controls which rows go into aggregation calculations), while HAVING selects the grouped rows after grouping and aggregation. Therefore, the WHERE clause cannot contain an aggregation function; Because it doesn't make sense to try to use the aggregation function to determine which rows are input to the aggregation operation. In contrast, the HAVING clause always contains the aggregation function. (strictly speaking, you can write HAVING clauses that don't use aggregation, but that's just a waste of time. The same conditions can be applied more efficiently to the WHERE phase. In the previous example, we can apply the city name restriction in WHERE because it does not require aggregation. This is more efficient than adding restrictions in HAVING, because we avoid grouping and aggregation calculations for rows that have not passed the WHERE check. where does all the data to work. Again, having can use aggregate functions such as having sum(qty) > 1000
The having sentence allows us to filter the data into groups. The where sentence filters the records before aggregation, that is, before the group by and having sentences. The having clause filters the group records after aggregation.
SQL instances:
1. Show the total population and total area of each region
SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region
Divide the returned records into groups using region, which is the literal meaning of GROUP BY. After grouping, the aggregation function is then used to perform operations on different fields (1 or more records) in each group.
2. Show the total population and total area of each region. Only areas larger than 1 million are shown
SELECT region, SUM(population), SUM(area)FROM bbcGROUP BY regionHAVING SUM(area) > 1000000
Here, we cannot use where to filter more than 1,000,000 regions, because such a record does not exist in the table. In contrast, the having clause allows us to filter groups of data into groups
mysql determines the length of a field
select home_page from aaa where char_length(trim(home_page))1;
The difference between the where and having clauses in mysql
Both the where and having clauses in mysql can be used to filter records, but there are some differences in their usage. See example 1:
Use the group by and having clauses to find non-duplicating records. sql is as follows:
select uid,email,count(*) as ct from `edm_user081217` GROUP BY email
And then when you look at this, it makes sense
select uid,email,count(*) as ct from `edm_user081217` GROUP BY email HAVING ct > 1
First, group by is used to group email, and then having is used to filter the records greater than 1, so that duplicate records can be found
Differences between having and where:
Select city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
The object of action is different. The WHERE clause applies to tables and views, and the HAVING clause applies to groups. WHERE selects the input rows before grouping and aggregation calculations (thus, it controls which rows go into aggregation calculations), while HAVING selects the grouped rows after grouping and aggregation. Therefore, the WHERE clause cannot contain an aggregation function; Because it doesn't make sense to try to use the aggregation function to determine which rows are input to the aggregation operation. In contrast, the HAVING clause always contains the aggregation function. (strictly speaking, you can write HAVING clauses that don't use aggregation, but that's just a waste of time. The same conditions can be applied more efficiently to the WHERE phase. In the previous example, we can apply the city name restriction in WHERE because it does not require aggregation. This is more efficient than adding restrictions in HAVING, because we avoid grouping and aggregation calculations for rows that have not passed the WHERE check. where does all the data to work. Again, having can use aggregate functions such as having sum(qty) > 1000