SQL语句--group by/having

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);