目录
select语句中,where、group by、having子句和聚合函数的执行次序如下:
第1关:GROUP BY 与聚合函数
GROUP BY
与聚合函数的使用
基本格式:
select [聚合函数] 字段名 from 表名 [where 查询条件] [group by 字段名]
在
select
指定的字段要么就要包含在Group By
语句的后面,作为分组的依据;要么就要被包含在聚合函数中。
编程要求:对年级Id
和性别进行分组,分别统计表中2、3、4
年级的男生总人数和女生总人数。
student
表数据结构如下:
stuId | gradeId | sex |
---|---|---|
0201 | 2 | 男 |
0221 | 2 | 男 |
0319 | 3 | 女 |
0508 | 5 | 男 |
0610 | 6 | 女 |
0101 | 1 | 男 |
0224 | 2 | 女 |
0413 | 4 | 女 |
预期输出:
gradeId sex count(*)
2 女 1
2 男 2
3 女 1
4 女 1
#查询表中2,3,4年级中分别男女的总人数
select gradeId,sex,count(*)
from student
where gradeId in(2,3,4)
group by gradeId,sex;
第2关 使用HAVING与ORDER BY
使用having
子句进行分组筛选
having
子句用来对分组后的数据进行筛选,即having
针对查询结果中的列发挥筛选数据作用。因此having
通常与Group by
连用。
基本格式:
select [聚合函数] 字段名 from 表名 [where 查询条件] [group by 字段名] [having 字段名 筛选条件]
select
语句中,where
、group by
、having
子句和聚合函数的执行次序如下:
1.where
子句从数据源中去除不符合条件的数据;
2.然后group by
子句搜集数据行到各个组中;
3.接着统计函数为各个组计算统计值;
4.最后having
子句去掉不符合其组搜索条件的各组数据行。
Having
与Where
的区别
where
子句都可以用having
代替,区别在于where
过滤行,having
过滤分组;
-
where
子句的作用是在对查询结果进行分组前,将不符合where
条件的行去掉,即在分组之前过滤数据,where
条件中不能包含聚组函数,使用where
条件过滤出特定的行; -
having
子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having
条件过滤出特定的组,也可以使用多个分组标准进行分组。
Group By
和 Order By
基本格式
select [聚合函数] 字段名 from 表名 [where 查询条件] [group by 字段名] [order by 字段名 排序方向]
编程要求:
-
我们要评选三好学生,条件是至少有两门课程在
90
分以上(包括90分)才能有资格,请列出符合的学生的学号(sno
)及其90
分以上(包括90分)科目总数; -
学校评选先进学生,要求平均成绩大于
90
分(包括90分)的学生都有资格,并且语文课必须在95
分以上(包括95分),请列出有资格的学生的学号(sno
)及其科目的平均分。
给定数据表tb_grade
格式如下:
sno | pno | score |
---|---|---|
1 | 语文 | 95 |
1 | 数学 | 98 |
1 | 英语 | 90 |
2 | 语文 | 89 |
2 | 数学 | 91 |
2 | 英语 | 92 |
3 | 语文 | 85 |
3 | 数学 | 88 |
3 | 英语 | 96 |
4 | 语文 | 95 |
4 | 数学 | 89 |
4 | 英语 | 88 |
预期输出:
sno count(*)
1 3
2 2
sno avg(score)
1 94.3333
4 90.6667
USE School;
#请在此处添加实现代码
########## Begin ##########
#1.查询表中至少有两门课程在90分以上的学生信息
select sno,count(*)
from tb_grade
where score>=90 group by sno having count(*)>=2;
#2.查询表中平均成绩大于90分且语文课在95分以上的学生信息
select sno,avg(score)
from tb_grade
where sno in(select sno
from tb_grade
where pno='语文' and score >= 95) group by sno having avg(score)>90;
########## End ##########