roupby (查询要求中通常出现“每”字眼)
group by语句用来与聚合函数(例如COUNT, SUM, AVG, MAX,MIN)联合使用,来得到一个或多个列的结果集。
注意:因为聚合函数通过作用于一组数据而只返回一个单个值,因此,在SELECT语句中出现的元素要么为一个聚合函数的输入值,要么为GROUP BY语句的参数,否则会出错。
例如,我们做一个这样的查询:
SELECT id, COUNT(course) as numcourse, score
FROM student
GROUP BY id
此时查询便会出错,错误提示如下:
Column ‘student.score' is invalid in the select list becauseit is not contained in either an aggregate function or the GROUP BY clause.
出现以上错误的原因是因为一个学生id对应多个分数,如果我们简单的在SELECT语句中写上score,则无法判断应该输出哪一个分数。如果想用score作为select语句的参数可以将它用作一个聚合函数的输入值,如下例,我们可以得到每个学生所选的课程门数以及每个学生的平均分数:
SELECT id, COUNT(course) as numcourse, AVG(score) as avgscore
FROM student
GROUP BY id
having:(通常查询中会对分组结果的显示提出要求)
HAVING语句通常与GROUP BY语句联合使用,用来过滤由GROUP BY语句返回的记录集。HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足
注意:having 子句中的每一个元素也必须出现在select列表中。有些数据库例外,如oracle。having是分组(group by)后的筛选条件,分组后的数据组内再筛选where则是在分组前筛选。
同样如果想查询平均分高于80分的学生选课数可以这样写:
SELECT id, COUNT(course) as numcourse, AVG(score) as avgscore
FROM student
GROUP BY id
HAVING AVG(score)>=80;
在这里,如果用WHERE代替HAVING就会出错
注意:当同时含有where子句、group by 子句 、having子句及聚集函数时,执行顺序如下:执行where子句查找符合条件的数据;使用group by 子句对数据进行分组;对group by 子句形成的组运行聚集函数计算每一组的值;最后用having 子句去掉不符合条件的组。
SQL实例:
一、显示每个地区的总人口数和总面积.
SELECTregion, SUM(population), SUM(area)
FROM bbc# `& e4 k' X* n1 v% ?+ |
GROUP BY region
先以region把返回记录分成多个组,这就是GROUP BY的字面含义。分完组后,然后用聚合函数对每组中的不同字段(一或多条记录)作运算。# B* i' z `, }* S, E5 i
二、显示每个地区的总人口数和总面积.仅显示那些面积超过1000000的地区。
SELECT region, SUM(population),SUM(area)7 ]; Z& I! t% i
FROM bbc8 F4 w2 v( P- f
GROUP BY region
HAVING SUM(area)>1000000# y" P z. O7 D9 `# X
三、学生信息查询
SQL> select * from sc;
SNOPNO GRADE
---------- ----- ----------
1 YW 95
1 SX 98
1 YY 90
2YW 89
2 SX 91
2 YY 92
3YW 85
3 SX 88
3 YY 96
4 YW 95
4 SX 89
这个表所描述的是4个学生对应每科学习成绩的记录,其中SNO(学生号)、PNO(课程名)、GRADE(成绩)。
1、显示90分以上学生的课程名和成绩
//这是一个简单的查询,并没有使用分组查询
SQL> select sno,pno,grade from sc where grade>=90;
SNOPNO GRADE
---------- ----- ----------
1YW 95
1 SX 98
1 YY 90
2 SX 91
2 YY 92
3 YY 96
4YW 95
已选择7行。
2、显示每个学生的成绩在90分以上的各有多少门
//进行分组显示,并且按照where条件之后计数
SQL> select sno,count(*) from sc where grade>=90group by sno;
SNO COUNT(*)
---------- ----------
1 3
2 2
4 1
3 1
3、这里我们并没有使用having语句,接下来如果我们要评选三好学生,条件是至少有两门课程在90分以上才能有资格,列出有资格的学生号及90分以上的课程数。
//进行分组显示,并且按照where条件之后计数,在根据having子句筛选分组
SQL> select sno,count(*) from sc where grade>=90group by sno having count(*)>=2;
SNO COUNT(*)
---------- ----------
1 3
2 2
这个结果是我们想要的,它列出了具有评选三好学生资格的学生号,跟上一个例子比较之后,发现这是在分组后进行的子查询。
4、学校评选先进学生,要求平均成绩大于90分的学生都有资格,并且语文课必须在95分以上,请列出有资格的学生
//实际上,这个查询先把语文大于95分的学生号提取出来,之后求平均值,分组显示后根据having语句选出平均成绩大于90的
SQL> select sno,avg(grade) from sc where SNO IN (SELECTSNO FROM SC WHERE GRADE>=95 AND PNO='YW') group by sno havingavg(grade)>=90;
SNO AVG(GRADE)
---------- ----------
1 94.3333333
4 90.6666667
5、查询比平均成绩至少比学号是3的平均成绩高的学生学号以及平均分数
//having子句中可进行比较和子查询
SQL> select sno,avg(grade) from sc
group by sno
having avg(grade) > (select avg(grade)from sc where sno=3);