文章目录
having案例解析
问题1:查询sc表里总成绩不低于300分的学生的学号和总成绩;
如果将SUM(degree)>=300将在where条件后面,会出现错误:Invaild use of group function;
having:分组之后的结果进行筛选;
#错误示范
select sno,sum(degree) from sc
where sum(degree)>=300
group by sno;
(1)where后面不能使用统计函数;
(2)where 是对分组之前的结果进行筛选;
注意点:
(1)having 在group by后面使用
(2)having是对分组结果进行筛选;
注意点:
1.where肯定在group by 之前
2.where后的条件表达式里不允许使用聚合函数,而having可以。
问题2:什么时候用having?
答:需要对分组后的结果进行筛选时用having;而where是对分组前的结果进行筛选;
问题3:什么时候用having、什么时候用where?
(1)当需要先筛选,然后再分组的时候就用where
(2)当需要对分组后的结果进行筛选的时候,就用having
需要注意having和where的用法区别
(1)位置上的区别
① having只能用在group by之后,对分组后的结果进行筛选(即使用having的前提条件是分组)。
② where肯定在group by 之前
(2)意义上的区别
① where是先筛选,筛选后再用group by进行分组;
② having是对分组后的结果进行筛选;
(3)语法格式上的区别
where后的条件表达式里不允许使用聚合函数,而having可以。
实例:group by和having一块使用
例1:查询sc表里平均成绩高于80分的学生的学号以及平均成绩;
select sno,avg(degree) from sc
group by sno
having avg(degree)>80;
例2:查询sc表里总成绩在300-400之间的学生的学号和总成绩;
having 字段 between 最小值 and 最大值;
select sno,sum(degree) from sc
having sum(degree) between 300 and 400;
例3:查询student表里男生或者女生人数超过100人的系别,以及人数有多少?
select sdept,ssex,count(*) from student
group by sdept,ssex
having count(*)>100;
例4:查找student表里重复的名字
select sname,count(*) from student
group by sname
having count(*)>1;
实例:group by和where以及having一块使用
where-group by-having-order by-limit
例5:查询student表里班级男生超过30人的班级以及男生人数。
select classno,ssex,count(*) from student
where ssex="男"
group by classno having count(*)>30;
例6:查询student表里年龄在33-40之间学生人数超过20人的系别以及对应的人数;
select sdept,count(*) from student
where timestampdiff(year,sbirthday,now()) between 33 and 40
group by sdept
having count(*)>20;
例7:查询平均成绩高于80分的学生的学号以及平均成绩并按照平均成绩降序排列;
(group,by,having,order by)
select sno,avg(degree) from sc
group by sno
having avg(degree)>80
order by avg(degree) desc;
例8:查询平均成绩高于80分的学生的学号以及平均成绩并按照平均成绩降序排列,只显示前10行记录(只显示平均成绩最高的前十名同学);(groupby,having,order by,limit)
select sno,avg(degree) from sc
group by sno
having avg(degree)>80
order by avg(degree) desc
limit 10;
例9:查询班级男生超过30人的班级以及男生人数并按照人数降序排列;
(where,groupby,having,order by)
select classno,ssex,count(*) from student
where ssex="男"
group by classno
having count(*)>30
order by count(*) desc;
使用顺序:where → group by → having → order by→limit
分组查询:
分组查询,group by 分组查询;
group by 字段;字段记录一样的分到一组
例如:group by sno;学号一样的分到一组里面,代表同一个人
例如:group by sdept:系别一样记录分到一组里面,代表属于同一个系;
分组查询用于统计数据;
having题型综合
1、查询sc表里总成绩高于300分的学生的学号,总成绩并按照总成绩升序排列;
select sno,sum(degree) from sc
group by sno
having sum(degree)>300
order by sum(degree) asc;
2、查询sc表里学号在2007010101-2007030111之间,平均成绩高于80分的学生的学号以及平均成绩,并按照平均成绩降序排列;
select sno,avg(degree) from sc
where sno between 2007010101 and 2007030111
group by sno
having avg(degree)>80
order by avg(degree) desc;
3、查询student表里每个系年龄不超过40岁的学生人数,并按照人数降序排序;
select sdept,count(*) from student
where timestampdiff(year,sbirthday,now())<40
group by sdept
order by count(*) desc;
4、查询student表里每个系的学生人数,并按照人数降序排列,只显示前4行记录;
select sdept,count(*) from student
group by sdept
order by count(*) desc
limit 4;
5、查询sc表里平均成绩在80-90之间的学生的学号以及平均成绩,并按照平均成绩升序排列;
select sno,avg(degree) from sc
group by sno
having avg(degree) between 80 and 90
order by avg(degree) asc;
6、查询student表信息工程系,软件工程系,计算机工程系学号在2007010101
-2007030430学生超过50人的系别,学生数,并按照学生数升序排列;
select sdept,count(*) from student
where sdept="信息工程" or "软件工程" or "计算机工程"
and
sno between 2007010101 and 2007030430
having count(*)>50
order by count(*) asc;
7、查询sc表里学号在2007010101-2007030111之间,平均成绩高于80分的学生的学号以及平均成绩,并按照平均成绩降序排列,只显示平均成绩最高的前5条记录;
select snoo,avg(degree) from sc
where sno between 2007010101 and 2007030111
group by sno
having avg(degree)>80
order by avg(degree) desc
limit 5;