The HAVING clause in Mysql to filter the group records using instructions _mysql
Source: Internet
Author: User
the use of having The HAVING clause allows us to sift through the various data in the group, where the words filter the records before aggregation, that is, before the group by and the having words. The HAVING clause filters the group records after aggregation.
SQL instance:
first, show the total number and area of each area SELECT region, sum (population), sum (area) from BBC GROUP by region
First, you divide the return records into groups by region, 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 aggregate functions.
Show the total population number and area of each area. Show only those areas with an area of more than 1000000 SELECT region, sum (population), sum (area) from Bbcgroup to regionhaving sum (area) >1000000
Here, we cannot use where to filter more than 1000000 of the region because no such record exists in the table. Instead, the HAVING clause allows us to filter the groups of data after the group
MySQL determines the length of a field Select Home_Page from AAA table where Char_length (Trim (home_page)) 1;
The difference between 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, look at one example:
Use a combination of GROUP BY and having clauses to isolate records that are not duplicates, as in sql:
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 has ct > 1
Use GROUP by to group email, use having to filter more than 1, so find out is duplicate record. The following is
the difference between having and where:
Select city from weather WHERE Temp_lo = (select Max (Temp_lo) from weather);
The object of the action is different. The WHERE clause acts on the table and view, and the HAVING clause acts on the group. Where the input row is selected before grouping and aggregating calculations (therefore, it controls which rows enter the aggregation calculation), and having the row selected for grouping and aggregating. Therefore, the WHERE clause cannot contain a clustered function, because it makes no sense to attempt to use the aggregate function to determine which row inputs are to the aggregation operation. Instead, the HAVING clause always contains a clustered function. (Strictly speaking, you can write a HAVING clause that does not use aggregation, but this is 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 aggregated. This is more efficient than adding restrictions in the having, because we avoid grouping and aggregating the rows that do not pass the where check: having typically follows group by, performing a portion of the selection of the record group to work. Where is the execution of all data to work. Moreover having can use aggregate functions, such as having sum (qty) >1000
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:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.