When obtaining group information, if certain conditions need to be met, you can use having to filter group conditions.
select ... from ... where ... group by ...having...
Take the following table as an example:
gender is still used as the grouping standard here, but a condition must be added, and only those with an average score greater than 90 will be displayed [this is to add a condition to the grouping result].
select sex,avg(english) as english from student group by sex having avg(english)>=90;
Let's take a look at the result:
Because the average score of boys is less than 90, it is filtered out.
Syntax execution order:
from student;First know which table to check the content from
group by ...In what group, find the criteria for grouping
having ...What conditions are used to filter the grouped content
select ...What to take from eligible groups
Filter row records and group information
The filtering of row records is filtering for each record, and the filtering of group information is filtering for groups, which can appear at the same time. The row information is filtered first, and then the group information is filtered.
whereFilter rows, only row information can appear
havingFilter group, only group information can appear
select ... from ... where ... group by ... having ...
Order of execution:
group by ...
Here we are still
studentexperimenting with the previous table, but here we add a few pieces of data to it for easy operation.
insert into student values(7,'小红','女',19,86,70); insert into student values(8,'小兰','女',18,77,66); insert into student values(9,'小艳','女',17,69,58); insert into student values(10,'热巴','女',22,83,84); insert into student values(11,'赵丽颖','女',21,99,98); insert into student values(12,'鞠婧祎','女',24,62,76);
After adding, view all the data in the table as shown below:
Here is an example:
- Query the age range of students and the number of students in that age range
- Record condition: Only count the students whose math scores are greater than 60 points [
- Grouping conditions: Only record groups with 2 or more students in the age range【
Take a look at the specific implementation:
select age,count(age) from student where math>=60 group by age having count(age)>=2;
Execute it to see the result:
only 4 students who are 18 years old are eligible.