SQL语句--group by/having

本文介绍了SQL中group by和having语句的应用方法,通过具体示例展示了如何使用这些语句进行数据分组和筛选,特别关注于如何结合where子句、聚合函数等进行高效查询。

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值