转自:http://book.csdn.net/bookfiles/235/10023510864.shtml
在实际SQL应用中,经常需要进行分组聚合,即将查询对象按一定条件分组,然后对每一个组进行聚合分析。
8.3.1 GROUP BY子句创建分组
创建分组是通过GROUP BY子句实现的。与WHERE子句不同,GROUP BY子句用于归纳信息类型,以汇总相关数据。而为什么要使用GROUP BY子句创建分组呢?可通过下面这个简单例子来说明。
假如要从TEACHER表中查询所有男教师的平均工资,用前面介绍的聚合函数AVG(),实现代码如下:
SELECT AVG(SAL) AS boyavg_sal
FROM TEACHER
WHERE TSEX='男'
运行结果如图8.18所示。
图8.18 TEACHER表中查询所有男教师的平均工资
而如果同时需要查询所有女教师的平均工资,该如何处理呢?显然,采用上述方法只能在WHERE子句中改变查询条件,重新查询。而如果要在一次查询中,同时得到二者的查询结果,就需要以性别为基准,将表中的所有数据记录分组,即男教师组和女教师组,并分别对两组数据进行分析,即计算工资(SAL列)的平均值。
实现上述功能,就需要使用分组子句GROUP BY。包括GROUP BY子句的查询就称为组合查询。语法如下。
SELECT column, SUM(column)
FROM table
GROUP BY column
说明:GROUP BY子句依据column列里的数据对行进行分组,即具有相同的值的行被划为一组。它一般与聚合函数同时使用。当然,这里的SUM()函数也可以是其他聚合函数。所有的组合列(GROUP BY子句中列出的列)必须是来自FROM子句列出的表,不能根据实际值、聚合函数结果或者其他表达式计算的值来对行分组。
从TEACHER表中查询所有男教师的平均工资和所有女教师的平均工资,实现代码如下。
SELECT TSEX+'教师'AS TEACHER, AVG(SAL) AS avg_sal
FROM TEACHER
GROUP BY TSEX
运行结果如图8.19所示。
图8.19 TEACHER表中所有男教师和所有女教师的平均工资
下面分析一下DBMS执行该实例的步骤。
DBMS首先执行FROM子句,将表TEACHER作为中间表。
如果有WHERE子句,则根据其中的搜索条件,从中间表中去除那些值为False的列。这里没有WHERE子句,所以跳过该步。
根据GROUP BY子句指定的分组列即TSEX,将中间表中的数据进行分组。这里TSEX只有“男”和“女”,因此中间表中的数据被分成了两组,一组中TSEX的值为“男”,另一组中TSEX的值为“女”。
为每个行组计算SELECT子句中的值,并为每组生成查询结果中的一行。对于TSEX值为“男”的行组,SELECT子句中首先执行“TSEX+'教师'”,得到“男教师”列值,再执行“AVG(SAL)”,求得该行组中的SAL的均值,将这两个值作为结果表中的一条记录。同样,对TSEX值为“女”的行组,进行类似的操作得到另一条记录。
8.3.2 GROUP BY子句根据多列组合行
上节介绍的GROUP BY子句进行组合查询,在GROUP BY子句中只有一列,它是组合查询的最简单形式。如果表中的行组依赖于多列,只要在查询的GROUP BY子句中,列出定义组所需的所有列即可。
从TEACHER表中查询各个系男教师和女教师的人数。实现代码:
SELECT DNAME,TSEX, COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY DNAME,TSEX
ORDER BY DNAME
运行结果如图8.20所示。
图8.20 TEACHER表中各系男教师和女教师的人数
从结果中可以发现,只有计算机系列出了男教师和女教师的人数。而别的系,只列出了一个值,这是因为,在TEACHER表中,这些系中的教师只有一种性别,如生物系只有两个女教师,而没有男教师,系统就认为该行记录为NULL,所以生物系的男教师的人数记录就不包含在结果表中。
8.3.3 ROLLUP运算符和CUBE运算符
在使用GROUP BY子句根据多列组合行时,可以在GROUP BY子句中使用ROLLUP运算符和CUBE运算符,扩展查询结果。两者的主要不同在于,CUBE运算符扩展的信息要比ROLLUP运算符多,下面结合具体的实例讲解二者的使用及区别。
1.ROLLUP运算符的使用
使用ROLLUP运算符扩展实例19查询结果。实现代码:
SELECT DNAME,TSEX, COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY DNAME,TSEX WITH ROLLUP
ORDER BY DNAME
运行结果如图8.21所示。
图8.21 ROLLUP运算符扩展的组合查询结果
与实例19相比,增加了7行数据。其中一行(结果中的第1行)为TEACHER表中所有教师的总人数,另外还分别为各系(DNAME)分组增加了一行(结果中的第3、5、8、10、12、14行),统计了各系教师的总人数。
实例21 改变GROUP BY子句中列的排列顺序对ROLLUP运算符的影响
如果改变GROUP BY子句中列的排列顺序,使用ROLLUP运算符会得到不同的结果,如下面的代码:
SELECT DNAME,TSEX, COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY TSEX, DNAME WITH ROLLUP
ORDER BY DNAME
运行结果如图8.22所示。
图8.22 依据系名排序后的结果
与8.3.2节实例相比,结果集中增加了3行记录,其中一行(结果中的第3行)为TEACHER表中所有教师的总人数,而另外两行(结果中的第1行和第2行)为性别(TSEX)分组的人数统计,即所有男教师的数量和所有女教师的数量。
2.CUBE运算符的使用
使用CUBE运算符扩展实例19查询结果。实现代码:
SELECT DNAME,TSEX, COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY DNAME,TSEX WITH CUBE
ORDER BY DNAME
运行结果如图8.23所示。
图8.23 使用CUBE运算符扩展的组合查询结果
从结果中可以发现,通过使用CUBE运算符,结果集中除了包含多列组合(DNAME和TSEX)的统计结果外,还包含了整表(TEACHER表)的统计结果和各单列(DNAME、TSEX)的统计结果。
8.3.4 GROUP BY子句中的NULL值处理
当GROUP BY子句中用于分组的列中出现NULL值时,将如何分组呢?按照前面的介绍,NULL不等于NULL(在WHERE子句中有过介绍)。然而,在GROUP BY子句中,却将所有的NULL值分在同一组,即认为它们是“相等”的。
从TEACHER表中查询所有的工资数及各工资的人数。实现代码:
SELECT SAL,COUNT(*) AS TOTAL_NUM
FROM TEACHER
GROUP BY SAL
ORDER BY SAL
运行结果如图8.24所示。
图8.24 TEACHER表中所有的工资数及各工资的人数
可见,SAL列中的两行NULL值被归为了一组。
8.3.5 HAVING子句
GROUP BY子句分组,只是简单地依据所选列的数据进行分组,将该列具有相同值的行划为一组。而实际应用中,往往还需要删除那些不能满足条件的行组,为了实现这个功能,SQL提供了HAVING子句。语法如下。
SELECT column, SUM(column)
FROM table
GROUP BY column
HAVING SUM(column) condition value
说明:HAVING通常与GROUP BY子句同时使用。当然,语法中的SUM()函数也可以是其他任何聚合函数。DBMS将HAVING子句中的搜索条件应用于GROUP BY子句产生的行组,如果行组不满足搜索条件,就将其从结果表中删除。
注意
|
前面介绍的有关WHERE子句的所有操作,如使用连接符、通配符、函数等,在HAVING子句中都可以使用。
|
从TEACHER表中查询至少有两位教师的系及教师人数。实现代码:
SELECT DNAME, COUNT(*) AS num_teacher
FROM TEACHER
GROUP BY DNAME
HAVING COUNT(*)>=2
运行结果如图8.25所示。
图8.25 TEACHER表中至少有两位教师的系及教师人数
8.3.6 HAVING子句与WHERE子句
HAVING子句和WHERE子句的相似之处在于,它也定义搜索条件。但与WHERE子句不同,HAVING子句与组有关,而不是与单个的行有关。
如果指定了GROUP BY子句,那么HAVING子句定义的搜索条件将作用于这个GROUP BY子句创建的那些组。
如果指定WHERE子句,而没有指定GROUP BY子句,那么HAVING子句定义的搜索条件将作用于WHERE子句的输出,并把这个输出看作是一个组。
如果既没有指定GROUP BY子句也没有指定WHERE子句,那么HAVING子句定义的搜索条件将作用于FROM子句的输出,并把这个输出看作是一个组。
在SELECT语句中,WHERE和HAVING子句的执行顺序不同。在本书的5.1.2节介绍的SELECT语句的执行步骤可知,WHERE子句只能接收来自FROM子句的输入,而HAVING子句则可以接收来自GROUP BY子句、WHERE子句和FROM子句的输入。
下面通过几个实例讲解HAVING子句和WHERE子句的不同作用。
实例25 HAVING子句和WHERE子句的不同作用
从TEACHER表中查询有女教师的系及拥有的女教师数量。实现代码:
SELECT DNAME, COUNT(TSEX) AS num_girl
FROM TEACHER
WHERE TSEX='女'
GROUP BY DNAME
运行结果如图8.26所示。
图8.26 TEACHER表中具有女教师的系及拥有的女教师数量
可见得到了3个系,与TEACHER表中数据相吻合。如果在上例中不使用WHERE子句,而是使用HAVING子句,教师限制为女教师,如下面的代码:
SELECT DNAME, COUNT(TSEX) AS num_girl
FROM TEACHER
GROUP BY DNAME
HAVING TSEX='女'
执行该代码,系统会给出以下出错提示信息。
Column 'TEACHER.TSEX' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
不能把单个的TSEX的值应用于组,包括在HAVING子句中的列必须是组列。因此,在这种情况下,WHERE子句就不可能用HAVING子句代替。
在数据的分组聚合分析中,HAVING子句与WHERE子句也可以共存。WHERE子句在分组之前过滤数据,而HAVING子句则过滤分组后的数据。
实例26 HAVING子句与WHERE子句联合使用
查询至少有两名女教师的系及拥有的女教师数量。实现代码:
SELECT DNAME, COUNT(TSEX) AS num_girl
FROM TEACHER
WHERE TSEX='女'
GROUP BY DNAME
HAVING COUNT(TSEX)>=2
运行结果如图8.27所示。
图8.27 TEACHER表中至少有两名女教师的系及拥有的女教师数量
这里通过HAVING子句对分组结果进行搜索,去除了不满足搜索条件(即只有一个教师的经济管理系)的行。
通常情况下,HAVING子句都与GROUP BY子句一起使用,这样就可以聚合相关数据,然后筛选这些数据,以进一步细化搜索。然而,如果没有GROUP BY子句,HAVING子句也可以单独使用。
如下面的代码:
SELECT COUNT(TSEX) AS num_girl
FROM TEACHER
WHERE TSEX='女'
HAVING COUNT(TSEX)>4
运行结果如图8.28所示。
图8.28 单独使用HAVING子句的查询结果
上述代码实现的功能实际上是从教师表中查询所有女教师的数量,如果女教师的数量大于4,则将其作为查询结果,而如果数量少于或者等于4,那么查询结果将为空值。当然,这种不使用GROUP BY子句而使用HAVING子句的情况,在实际应用中很少用到。
8.3.7 SELECT语句各查询子句总结
至此,SELECT语句中的所有子句都介绍完了,它们在SELECT查询语句中的排列顺序及主要作用如表8-2所示。
表8-2 SELECT查询语句及其所有子句
顺 序 号
|
子句关键词
|
子 句 功 能
|
1
|
SELECT
|
从指定表中取出指定的列的数据
|
2
|
FROM
|
指定要查询操作的表
|
3
|
WHERE
|
用来规定一种选择查询的标准
|
4
|
GROUP BY
|
对结果集进行分组,常与聚合函数一起使用
|
5
|
HAVING
|
返回选取的结果集中行的数目
|
6
|
ORDER BY
|
指定分组的搜寻条件
|
如果在同一个SELECT查询语句中,用到了表8-2所示的一些查询子句,则各查询子句的排列就依照它们的顺序号由低到高的顺序。因此,完整的SELECT查询语句可以表示为:
SELECT select_list
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
其中[ ]中的部分为可选项。
从TEACHER表中查询至少有两名女教师的系及拥有的女教师数量,并按女教师的数量升序的顺序排列结果。实现代码:
SELECT DNAME, COUNT(TSEX) AS num_girl
FROM TEACHER
WHERE TSEX='女'
GROUP BY DNAME
HAVING COUNT(TSEX)>=2
ORDER BY num_girl
运行结果如图8.29所示。
图8.29 对图8.27中按数量升序的排列结果
分享到:
相关推荐
sql自定义函数,用于合并字符串列,可以在分组的情况下对varchar类型的字段合并
Oracle_PL(SQL)单行函数和组函数及使用
Oracle数据库使用分组函数来对数据进行聚集
SQL常见函数实际操作应用,可以作为参考用
SQLServer和Oracle的常用函数对比
使用Oracle的SQL函数实现工作日和节假日分组统计.pdf
partition by关键字是分析性函数的一部分,partition by用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组,本文给大家介绍SQLServer中Partition By及row_number 函数使用详解,需要的朋友参考下
sql基础之分组查询和聚合函数,简洁的描述了查询的语法和规则,列举实例容易理解。
sql plsql 函数学习 Oracle 快速入门 有关表的操作 运算符 常用 ORACLE 函数(日期函数、字符函数、数字函数、转换函数、分组函数、其他函数……) 临时表 join SEQUENCE 同义词 等等
使用分组,排序和聚合函数以及连接查询 1.掌握通配符的用法 2.掌握 GROUP BY 子句的使用 3.掌握 ORDER BY子句的使用 4.掌握 DISTINCT关键字的使用 5.掌握聚集函数的使用
本博客简单介绍一下oracle分组函数之rollup的用法,rollup函数常用于分组统计,也是属于oracle分析函数的一种 环境准备 create table dept as select * from scott.dept; create table emp as select * from ...
以上只是SQL开窗函数的一些常用功能示例,实际上开窗函数的功能非常强大,并且可以与其他聚合函数、分组函数等组合使用,以满足更复杂的计算需求。在使用开窗函数时,需要根据具体的需求来选择合适的开窗函数以及...
面试中的经典sql面试题 工作中经典的sql使用场景 学习sql经典的实例 了解sql常用函数、分组等技巧
连接查询与分组聚集函数.sql
常用函数使用 第三部分 高级SQL的编写 常用函数使用 第三部分 高级SQL的编写 常用函数使用 第三部分 高级SQL的编写 常用函数使用 第三部分 高级SQL的编写 常用函数使用 第三部分 高级SQL的编写 常用函数使用 第三...
第22讲 SQL语言高级05--分组统计函数.PPT第22讲 SQL语言高级05--分组统计函数.PPT第22讲 SQL语言高级05--分组统计函数.PPT第22讲 SQL语言高级05--分组统计函数.PPT
SQL中的单记录函数 1.ASCII 返回与指定的字符对应的十进制数; SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; A A ZERO SPACE --------- --------- --------- --------- ...
关于SQL常用函数的系统整理,主要涉及连接函数、分组函数、聚合函数、字符函数、数字函数、时间函数,希望可以对你有帮助
分组后分组合计以及总计SQL语句(稍微整理了一下)MYSQL每隔10分钟进行分组统计的实现方法mysql使用GROUP BY分组实现取前N条记录的方法详解MySQL中的分组查询与连接查询语句sql server如何利用开窗函数over()进行...
│ 1.9.2(2) 使用文件及文件组.sql │ 1.9.2(3) 调整tempdb数据库的文件属性.sql │ ├─第02章 │ │ 2.1 日期概念理解中的一些测试.sql │ │ 2.2.4 CONVERT在日期转换中的使用示例.sql │ │ 2.3.3 SET ...