Preface ：
Author's brief introduction ： Husky who yearns for power , You can call me Siberian Husky , One devoted to TFS - The whole stack Empowered bloggers
CSDN Blog expert certification 、 Nova plan Season 3 full stack track top_1 、 Huawei cloud sharing expert 、 Alibaba cloud expert Blogger
If there is something wrong with the knowledge of the article , Please correct me. ！ Learn with you , Progress together
The motto of life ： Being superior to some other man , Is not high , The true nobility is to be superior to one's former self .
If you feel the blogger's article is good , Please pay attention. 、 give the thumbs-up 、 Collect three companies to support bloggers

---

Column series （ Click unlock ） Learning route （ Click unlock ） Knowledge orientation Python Full stack white paper Zero foundation beginner level chapter Easy to get started in an easy to understand way , Let you fall in love with Python The charm of . Advanced Grammar Mainly around multi-threaded programming 、 Regular expression learning 、 Including project exercises close to actual combat . Office automation Realize the automatic operation of daily office software , Save time 、 Improve office efficiency . Automation test practice From the perspective of actual combat , One step ahead , Rapid transformation test development engineer . Database development practice Master the knowledge of relational and non relational databases , Improve the actual development ability of the database . Introduction and practice of reptile Updating Data analysis Updating Introduction to the front +flask Full stack Updating django+vue Full stack Updating expand - Introduction to artificial intelligence Updating The road to network security Stepping on a pit Record the pit encountered during learning and drilling , It is convenient for those who come from behind to catch up Net security literacy Three days fishing , No deep understanding of the principle , It will only make you a script boy . vulhub The loopholes in the shooting range reappear Make it easy to reproduce vulnerabilities , Let security researchers focus more on the vulnerability principle itself . shell Programming Don't involve linux Basics , The final case will be in the direction of safety reinforcement . [ To be finished ] WEB Vulnerability attack and defense 2021 year 9 month 3 Stop updating on , Move to safe communities such as prophet community and small secret circles Collection of penetration tools 2021 year 9 month 3 Stop updating on , Move to safe communities such as prophet community and small secret circles A little Engineer Test artifact - Charles Software test data packet capture analysis artifact Test artifact - Fiddler Learn to fiddle , Learn not to stand upside down and eat , Rarefied ！ Test artifact - Jmeter Not just a performance test artifact , It can also be used to build a lightweight interface automation testing framework . RobotFrameWorkPython Automatic test tool realized , This chapter only introduces UI Automation part . Java Realization UI automation Document written in 2016 year ,Java Realized UI automation , It still has reference significance . MonkeyRunner There are not many application scenarios for this tool at present , The document has been deleted , Only for the sake of good typesetting .

List of articles

• Difficulties encountered in grouping query
• HAVING The purpose of the clause
• HAVING Special usage of clause

In this chapter, let's learn “HAVING” Clause , This “HAVING” Clauses should be and “GROUP BY” Only when combined can it be used , Not to be used alone “HAVING” Clause . Next, let's take a look at why we should introduce “HAVING” The grammar of .

Difficulties encountered in grouping query

Actually , introduce “HAVING” Clause is also out of a helpless , Because some conditional queries are used “GROUP BY” Can't meet the requirements . For example, the following query statement .

The average base salary of the inquiry Department exceeds 2000 The number of the Department , According to normal logic, it should be written like this , See below SQL：

SELECT deptno
FROM t_emp
WHERE AVG(sal) >= 2000
GROUP BY deptno;

PS： Grammatically , No problem . But in execution , There must be errors in grammar . What kind of thing is this ？

PS： This is because "WHERE" The execution order priority of clauses is higher than "GROUP BY" Of , One but "WHERE" Aggregate function appears in Clause , that MySQL I don't know the scope of this aggregate function to count the summary values , So there are grammatical mistakes .

PS： Only in "GROUP BY" After execution , Aggregate function can determine the scope , Then calculate the results that need to be aggregated . So the above SQL sentence The mistake is WHERE Clause has an aggregate function .

---

So how to solve this problem ？ This is the time to introduce “HAVING” Clause .

SELECT deptno
FROM t_emp
GROUP BY deptno HAVING AVG(sal);

Because of WHERE Clause in GROUP BY Before clause , Now WHERE The aggregate function of clause does not know what range to count data , So we must put WHERE The aggregate function of clause takes away , Put it in GROUP BY Wait for the statistical range after the function , Then calculate the statistics .

PS："HAVING The clause is followed by "GROUP BY" The use of , It cannot be used independently . In the use of "HAVING" When making aggregate function statistics ,"GROUP BY" The statistical range has been determined .

HAVING The purpose of the clause

“HAVING” Clause and “WHERE” The use of clauses is similar , It is also used for conditional screening . It's just "HAVING The clause is written in “GROUP BY” It's used later ,“GROUP BY” After execution , “HAVING” Clause will execute , therefore “HAVING” Clause, we can use Aggregate functions Make a conditional judgment .

SQL sentence "GROUP_CONCAT" Examples are as follows :（ Check in each department ,1982 The number of employees employed after years exceeds 2 Personal department number ）

SELECT deptno
FROM t_emp
WHERE hiredate >= "1982-01-01"
GROUP BY deptno HAVING COUNT(*) >= 2
ORDER BY deptno ASC;
-- If the result set is unordered , have access to ORDER BY Sort , If it's orderly , Can also be ORDER BY Get rid of .

HAVING Special usage of clause

In the use of "GROUP BY" In clauses , According to the figures "1" grouping .MySQL Will be based on "SELECT" The fields of the first column in the clause are grouped , That's right "HAVING" Clauses can also be used normally .

SQL The statement demonstration case is as follows :

SELECT deptno, COUNT(*)
FROM t_emp
GROUP BY 1 ;

---

SELECT deptno, COUNT(*)
FROM t_emp
GROUP BY 1
HAVING deptno IN(10, 20);
-- Under normal circumstances , If not "HAVING" In clauses , We can use "WHERE" Clause .
-- although "WHERE" Clause And "HAVING" Clauses with similar functions can be filtered , But it is not recommended "HAVING" Clause to perform such conditional filtering .
-- because "WHERE" Clauses precede "GROUP BY" Clause to execute , First filter out some data , Then group the qualified data 、 Statistical calculation of aggregation .
-- In this way , The amount of data becomes much less . But if you write the filter criteria in "HAVING" In the clause , This data volume is very large , Use "HAVING" Screening is not worth it .

PS： So let's say ,"WHERE" Clauses cannot be "HAVING" Alternative , introduce "HAVING" The purpose of clause is to use aggregate functions and ordinary data for conditional judgment , You have to use "HAVING" Clause , In other cases , Use "WHERE" Clause , Or use "WHERE" The clause is good .