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

### SQL中的HAVINGGROUP BY子句 #### GROUP BY 子句的作用 `GROUP BY` 子句用于将查询的结果集按照一个或多个列分组,通常与聚合函数一起使用来计算每组的数据汇总信息。例如,在订单表中按会员ID (`member_id`) 进行分组可以统计每位会员下的不同订单数量。 ```sql SELECT member_id, COUNT(DISTINCT order_id) AS distinct_order_count FROM orders GROUP BY member_id; ``` 此语句会返回每个 `member_id` 及其对应的唯一订单数[^2]。 #### HAVING 子句的功能 `HAVING` 子句用来过滤由 `GROUP BY` 创建的各组数据。它类似于 `WHERE` 子句,但是作用于已经完成分组后的记录集合上,并且可以在条件表达式里包含聚合函数。这意味着如果想要基于某个聚合结果设置筛选标准,则必须通过 `HAVING` 来实现而不是 `WHERE`。 考虑这样一个场景:找出至少有两次购买行为的不同客户列表: ```sql SELECT member_id FROM orders GROUP BY member_id HAVING COUNT(order_id) >= 2; ``` 上述命令先依据 `member_id` 对订单进行了分类处理并计数;接着利用 `HAVING` 设置了一个门槛——只有那些拥有两个及以上订单条目的成员才会被选入最终输出结果集中[^1]。 #### 主要差异对比 - **应用时机**:`WHERE` 是在执行任何分组操作之前应用于单个行上的条件判断;而 `HAVING` 则是在完成了分组之后针对整个组进行进一步的选择。 - **支持的操作**:由于 `HAVING` 处理的是经过分组后的数据,因此它可以安全地运用各种类型的聚合函数作为逻辑运算的一部分,这正是 `WHERE` 所不具备的能力之一。 - **性能考量**:当不需要做额外的过滤时仅需保留必要的 `GROUP BY`,因为引入不必要的 `HAVING` 或者复杂的组合可能会降低查询效率。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值