《SQL汇总和分组数据》由会员分享，可在线阅读，更多相关《SQL汇总和分组数据（13页珍藏版）》请在金锄头文库上搜索。
1、使用聚合函数进行汇总和分组SQL 提供一组聚合函数，它们能够对整个数据集合进行计算，将一组原始数据转换为有 用的信息，以便用户使用。例如求成绩表中的总成绩、学生表中平均年龄等。SQL的聚合函数如表1所示。表 1 聚合函数聚合函数支持的数据类型功能描述Sum()数字对指定列中的所有非空值求和avg()数字对指定列中的所有非空值求平均值min()数字、字符、日期返回指定列中的最小数字、最小的字符串和最早 的日期时间max()数字、字符、日期返回指定列中的最大数字、最大的字符串和最近 的日期时间count ( distinct *)任意基于行的数据类型统计结果集中全部记录行的数量，最多可达2 147
2、 483 647 行count_big(distinct *)任意基于行的数据类型类似于count()函数，但因其返回值使用了bigint 数据类型，所以最多可以统计2人63-1行1. SUM()函数和AVG()函数两个函数都是对列式数字型的进行计算，只不过SUM()是对列求和；而AVG()是对列 求平均值。示例：求“grade”表中每学期的课程成绩的总和。在查询分析器中输入的SQL语句如下：use studentselect sum(课程成绩)as总成绩from grade实现的过程如图1所示。学号1课程代号1课程成赣1学期1B0LI5K0295. 22B0LI3k0398. 31 严 1总
3、战绩ELI03k03 S8. 31L,/ 1556. 80.BLii：i4k0439.92PE002k0292. 42這询后BOlilKOI92. 71询前图 1 求课程成绩的总和当与GROUP BY子句一起使用时，每个聚集函数都为每一组生成一个值，而不是对整 个表生成一个值。示例：在“student”表中，按“性别”分别求年龄的平均值。在查询分析器中输入SQL语句如 下：use studentselect性别,avg(年龄)as平均年龄from student group by f生另U实现的过程如图2所示。学号I姓名 I性别 I年龄丨出生日期I联系方式I男男玄玄玄 伟 葱葱白 丈导月小葱小
4、 刘李刘李李李20 1986-01-01134516 1 2 4 5 n- n- _u _u -U o o o o O B b B E B20 1986-01-032345121 1985-03-035234522 1984-03-1062345221984-03-1023451241982-03-0382345笑询前图 2 男女生的平均年龄2MIN( )函数和 MAX( )函数MIN( )和 MAX( )函数分别查询列中的最小值和最大值。但列的数据包含数字、字符或 日期/时间信息。MIN()和MAX()函数结果与列中数据的数据类型完全相同。示例：查询“student”表中最早出生的学生。在查
5、询分析器中输入的SQL语句如下：use studentselect min（出生日期）as最早出生 from Student实现的过程结果如图3 所示。图 3 学生表中年龄最小的学生信息下面把GROUP BY子句和MAX()函数结合使用。示例：在“student”表中，按“性别”分别求年龄的最大值。在查询分析器中输入SQL语句如下：use studentselect性别,max(年龄)as最大年龄from Studentgroup by 性别实现的过程如图4所示。学号BIJLI31姓名剤丈伟1性别1年龄丨出生日期1联系方式13451201986-01-01B006李导201986-01-032
6、3451N .性别最大年龄bOOl刘月211985-03-0352345| 1 男 20B0LI2李小葱221984-03-10623454/2 女.24B004李葱葱22ldS-LlS-lLI23451B005李小白241982-03-0382345螢询后僅询前图 4 男女生中年龄的最大值3. COUNT()函数和 COUNT_big()函数COUNT()函数和COUNT_big()函数两个函数都是对列中数据值的数目进行计数。它们 返回的值总是一个整数，不管列的数据类型。示例：求“student”表中女生的人数。在查询分析器中输入的SQL语句如下：use studentselect coun
7、t(年龄)as女生记录总数from student where 性别=女实现的过程如图5 所示。学号I姓名 I性别 I年龄丨出生日期I联系方式B0LI3 刘丈伟201986-01-01BLILI6 bOOlE002E：0ri4B0LI5李导 刘月 李小葱 李葱葱 李小白2L212222241986-01-031985-03-031984-03-101984-03-101982-03-03134512345152345623452345182345倉询前图 5 “ Student ”表中女生的记录总数COUNT (*)就可以求整个表所有的记录数。例如，求“student”表中所有的记录数,SQL
8、语句如下:use studentselect count（*） from student4.消除重复记录(DISTINCT)指定DISTINCT关键字不但可以消除查询结果中的重复记录，而且在使用SUM( )、AVG() 和COUNT()聚合函数时，可以从列中消除重复的值。DISTINCT关键字和聚合函数使用的 格式是：聚合函数名称(DISTINCT列名)。示例:在“grade”表中，统计多少学生参加考试。在查询分析器中输入的SQL语句如下：use student selectcount（学号）from grade实现的过程如图6 所示。学号课程代号1课程成绩学期1E005K0295.22E00
9、3kH390. 31B003kj：388. 31EOO-489. 92B00292. 42EiLlOlKOI92.71笑询前无列名)I16螢询后图 6 统计参加考试的学生从上面的统计结果不难看出，实际上参加考试的学生是学号从B001B005共5名，其 中有重复的学号。这样为了正确统计到底有多少学生参加考试，就必须用到关键字DISTINCT。示例：在“”表中，统计多少学生参加考试。-r2 touse studentselect count（distinct 学号） from grade实现的过程如图7 所示。图 7 使用 DISTINCT 关键字统计参加考试的学生注意：当使用 DISTINCT
10、关键字时，聚合函数的参数必须是一个简单的列名。筛选分组结果用 GROUP BY 可以实现数据分组操作，但有时用户不需要对数据表中所有的数据进行 分组，这时就需要使用HAVING子句来筛选分组。示例：在“grade”表中，查询参加同一门课程考试的同学至少两个人的课程成绩总和。在查 询分析器中输入的SQL语句如下：use studentselect课程代号,sum(课程成绩)as课程总成绩from gradegroup by课程代号 having count(*)=2 实现的过程如图1所示。图 1 至少两个人对加同一门考试的课程成绩总和1. SQL SELECT语句的执行顺序 下面给出SQL SE
11、LECT语句的执行顺序。(1) FROM 子句组装来自不同数据源的数据。(2) WHERE 子句基于指定的条件对记录行进行筛选。(3) GROUP BY子句将数据划分为多个分组。(4) 使用聚集函数进行计算。( 5)使用 HAVING 子句筛选分组。(6) 计算所有的表达式。(7) 使用ORDER BY对结果集进行排序。 示例：在“grade”表中，把“学号”内容不为空的记录按照“学号”分组，并且筛选分组结 果，选出“课程成绩”大于92的学生信息。在查询分析器中输入的SQL语句如下：use studentselect学号,avg(课程成绩)as平均成绩from gradewhere 学号 is
12、 not null group by 学号 having avg（课程成绩）92 order by平均成绩 实现的过程如图2所示。学号1课程代号1课程成绩1学期_ B005K0295. 22学号平均成绩_ BIJIJJwjjya. j(NULLB00292.40.B0U3ldJ388. 3irEiOOl92.70.B004ldJ489. 92LtZ 3BOOS93. 29.B002ldJ292. 424B00595. 20._B001KOI92.71這询后茜询前图2 查询统计“student”表下面给出上个示例中SQL语句的执行顺序。（1）首先执行FROM子句，从“grade”表组装数据源的数
13、据。（2）执行WHERE子句，筛选“grade”表中所有数据不为NULL的数据。（3）执行GROUP BY子句，把“grade”表按“学号”列进行分组。（4）计算AVG（）聚集函数，按“课程成绩”求出平均成绩的具体数值。（5）执行HAVING子句，筛选课程的平均成绩大于92分的学生信息。（6）执行ORDER BY子句，把最后的结果按“平均成绩”进行排序。2. HAVING子句在分组搜索条件上的限制HAVING 子句指定的搜索条件必须是作为一个整体应用于组而不是应用于各个记录。所 以 HAVING 的搜索条件是有限制的，列举如下： 一个常量。 一个聚合函数，这个聚合函数生成一个值，该值汇总组中的记录。 一个分组列，按照定义，这个分组字段在这个组的每一记录中有同样的值。 一个包含上述各项组合的表达式。示例：在“grade”表中，按“学期”分组，求“学期”值不为空的课程成绩平均值。在查询 分析器中输入的SQL语句如下：use studentselect avg（课程成绩）as 平均成绩from gradegroup
