sql汇总和分组数据.pdf

使用聚合函数进展汇总和分组 SQL 提供一组聚合函数，它们可以对整个数据集合进展计算，将一组原始数据转换为有用的信息，以便用户使用。例如求成绩表中的总成绩、学生表中平均年龄等。SQL 的聚合函数如表 1 所示。表 1 聚合函数 聚合函数 支持的数据类型 功 能 描 述 Sum()数字 对指定列中的所有非空值求和 avg()数字 对指定列中的所有非空值求平均值 min()数字、字符、日期 返回指定列中的最小数字、最小的字符串和最早的日期时间 max()数字、字符、日期 返回指定列中的最大数字、最大的字符串和最近的日期时间 count distinct*任意基于行的数据类型 统计结果集中全部记录行的数量，最多可达2 147 483 647 行 count_bigdistinct*任意基于行的数据类型 类似于 count()函数，但因其返回值使用了 bigint数据类型，所以最多可以统计 263-1 行 1SUM()函数和 AVG()函数 两个函数都是对列式数字型的进展计算，只不过 SUM()是对列求和；而 AVG()是对列求平均值。例如：求“grade表中每学期的课程成绩的总和。在查询分析器中输入的 SQL 语句如下：use student select sum(课程成绩)as 总成绩 from grade 实现的过程如图 1 所示。图 1 求课程成绩的总和 当与 GROUP BY 子句一起使用时，每个聚集函数都为每一组生成一个值，而不是对整个表生成一个值。例如：在“student表中，按“性别分别求年龄的平均值。在查询分析器中输入的SQL 语句如下：use student select 性别,avg(年龄)as 平均年龄 from student group by 性别 实现的过程如图 2 所示。图 2 男女生的平均年龄 2MIN()函数和 MAX()函数 MIN()和 MAX()函数分别查询列中的最小值和最大值。但列的数据包含数字、字符或日期/时间信息。MIN()和 MAX()函数结果与列中数据的数据类型完全一样。例如：查询“student表中最早出生的学生。在查询分析器中输入的 SQL 语句如下：use student select min(出生日期)as 最早出生 from Student 实现的过程结果如图 3 所示。图 3 学生表中年龄最小的学生信息 下面把 GROUP BY 子句和 MAX()函数结合使用。例如：在“student表中，按“性别分别求年龄的最大值。在查询分析器中输入的SQL 语句如下：use student select 性别,max(年龄)as 最大年龄 from Student group by 性别 实现的过程如图 4 所示。图 4 男女生中年龄的最大值 3COUNT()函数和 COUNT_big()函数 COUNT()函数和 COUNT_big()函数两个函数都是对列中数据值的数目进展计数。它们返回的值总是一个整数，不管列的数据类型。例如：求“student表中女生的人数。在查询分析器中输入的 SQL 语句如下：use student select count(年龄)as 女生记录总数 from student where 性别=女 实现的过程如图 5 所示。图 5 “Student表中女生的记录总数 COUNT*就可以求整个表所有的记录数。例如，求“student表中所有的记录数，SQL 语句如下：use student select count(*)from student 4消除重复记录DISTINCT 指定DISTINCT关键字不但可以消除查询结果中的重复记录，而且在使用SUM()、AVG()和 COUNT()聚合函数时，可以从列中消除重复的值。DISTINCT 关键字和聚合函数使用的格式是：聚合函数名称DISTINCT 列名。例如：在“grade表中，统计多少学生参加考试。在查询分析器中输入的 SQL 语句如下：use student select count(学号)from grade 实现的过程如图 6 所示。图 6 统计参加考试的学生 从上面的统计结果不难看出，实际上参加考试的学生是学号从 B001B005 共 5 名，其中有重复的学号。这样为了正确统计到底有多少学生参加考试，就必须用到关键字DISTINCT。例如：在“grade表中，统计多少学生参加考试。在查询分析器中输入的 SQL 语句如下：use student select count(distinct 学号)from grade 实现的过程如图 7 所示。图 7 使用 DISTINCT 关键字统计参加考试的学生 注意：当使用 DISTINCT 关键字时，聚合函数的参数必须是一个简单的列名。挑选分组结果 用 GROUP BY 可以实现数据分组操作，但有时用户不需要对数据表中所有的数据进展分组，这时就需要使用 HAVING 子句来挑选分组。例如：在“grade表中，查询参加同一门课程考试的同学至少两个人的课程成绩总和。在查询分析器中输入的 SQL 语句如下：use student select 课程代号,sum(课程成绩)as 课程总成绩 from grade group by 课程代号 having count(*)=2 实现的过程如图 1 所示。图 1 至少两个人对加同一门考试的课程成绩总和 1SQL SELECT 语句的执行顺序 下面给出 SQL SELECT 语句的执行顺序。1FROM 子句组装来自不同数据源的数据。2WHERE 子句基于指定的条件对记录行进展挑选。3GROUP BY 子句将数据划分为多个分组。4使用聚集函数进展计算。5使用 HAVING 子句挑选分组。6计算所有的表达式。7使用 ORDER BY 对结果集进展排序。例如：在“grade表中，把“学号内容不为空的记录按照“学号分组，并且挑选分组结果，选出“课程成绩大于 92 的学生信息。在查询分析器中输入的 SQL 语句如下：use student select 学号,avg(课程成绩)as 平均成绩 from grade where 学号 is not null group by 学号 having avg(课程成绩)92 order by 平均成绩 实现的过程如图 2 所示。图 2 查询统计“student表 下面给出上个例如中 SQL 语句的执行顺序。1首先执行 FROM 子句，从“grade表组装数据源的数据。2执行 WHERE 子句，挑选“grade表中所有数据不为 NULL 的数据。3执行 GROUP BY 子句，把“grade表按“学号列进展分组。4计算 AVG()聚集函数，按“课程成绩求出平均成绩的详细数值。5执行 HAVING 子句，挑选课程的平均成绩大于 92 分的学生信息。6执行 ORDER BY 子句，把最后的结果按“平均成绩进展排序。2HAVING 子句在分组搜索条件上的限制 HAVING 子句指定的搜索条件必须是作为一个整体应用于组而不是应用于各个记录。所以 HAVING 的搜索条件是有限制的，列举如下：一个常量。一个聚合函数，这个聚合函数生成一个值，该值汇总组中的记录。一个分组列，按照定义，这个分组字段在这个组的每一记录中有同样的值。一个包含上述各项组合的表达式。例如：在“grade表中，按“学期分组，求“学期值不为空的课程成绩平均值。在查询分析器中输入的 SQL 语句如下：use student select avg(课程成绩)as 平均成绩 from grade group by 学期 having 学期 is not null 实现的过程如图 3 所示。图 3 求“grade表中按“学期分组的平均成绩 例如：在“grade表中，按“课程类别分组，并且查询“课程类别不是“计算机类的课程信息。在查询分析器中输入的 SQL 语句如下：use student select 课程类别 from course group by 课程类别 having 课程类别计算机类 实现的过程如图 4 所示。图 4 “grade表按“课程类别分组统计 3比较 HAVING 子句与 WHERE 子句 两个子句的相似之处。1它们都是从结果表中挑选数据。2它们都设置了某些数据能通过而其他数据不能通过的条件。两个子句的不同之处。1WHERE 子句可以在进展任何处理之前从原表、原始数据中挑选行。2HAVING 子句可以在进展绝大部分处理之后挑选已分组和已总结的数据。3WHERE 子句不能在它设置的条件之中使用列函数。4HAVING 子句可以在它的条件中使用列函数。理解HVING子句的最好方法就是记住SELECT语句中的哪些子句是按照明确的次序进展处理的。WHERE 子句只能接收来自 FROM 子句的输入，而 HAVING 子句那么可以接收来自 GROUP BY、WHERE 子句或 FROM 子句的输入。这是一个微妙但却重要的差异。例如：在“grade表中，把“课程成绩大于 92 分的按“学期分组求平均成绩。在查询分析器中输入的 SQL 语句如下：use student select 学期,avg(课程成绩)as 平均成绩 from grade where 课程成绩92 group by 学期 实现的过程如图 5 所示。图 5 按学期求大于 92 分的课程的平均成绩 上个例子，首先挑选出“课程成绩大于92 分的学生信息，然后按“学期再分组求课程成绩的平均值。下面把 WHERE 子句交换成 HAVING 子句，在查询分析器中运行的结果如图 6 所示。图 6 按“学期分组用 HAVING 子句设置条件 SQL 语句如下所示：use student select 学期,avg(课程成绩)as 平均成绩 from grade group by 学期 having 课程成绩92 由此可见，执行用 HAVING 子句交换 WHERE 子句的语句是错误的。因为“课程成绩列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。下面改变 HAVING 子句的条件，这个子句包括一个用了聚合函数的列。例如：在“grade表中，按“学期求课程成绩的平均值，并挑选出平均成绩大于 92 分的。在查询分析器中输入的 SQL 语句如下：use student select 学期,avg(课程成绩)as 平均成绩 from grade group by 学期 having avg(课程成绩)92 实现的过程如图 7 所示。图 7 按“学期求成绩的平均值并用 HAVING 进展挑选 当按“学期分完组后，HAVING 子句就应用于这些结果。对于每一个组来说，都要求成绩的平均值，但只有平均成绩大于 92 分才能包括在结果中。HAVING 对分组后的数据可以进展挑选，并且可以使用 AVG或 SUM之类的设置功能，而这些是在 WHERE 子句中无法使用的。4使用 ALL 关键字 在 GROUP BY 子句中使用 ALL 关键字。只有在 SQL 语句还包括 WHERE 子句时，ALL关键字才有意义。假设使用 ALL 关键字，那么查询结果将包括由 GROUP BY 子句产生的所有组，即使某些组没有符合查询条件的行。没有 ALL 关键字，包含 GROUP BY 子句的 SELECT 语句将不显示没有符合条件的行的组。例如：在“grade表中，按“课程代号分组求出课程的平均成绩，并不显示“课程成绩的值为 NULL 值的行。在查询分析器中输入的 SQL 语句如下：use student select 课程代号,avg(课程成绩)as 平均成绩 from grade where 学号!=b003 group by 课程代号 having 课程代号 is not null 实现的过程如图 8 所示。图 8 分组查询成绩表 在上面的例子中使用 ALL 关键字。例如：在“grade表中，按“课程代号分组求出课程的平均成绩，并不显示“课程成绩的值为 NULL 值。在查询分析器中输入的 SQL 语句如下：use student select 课程代号,avg(课程成绩)as 平均成绩 from grade where 学号!=b003 group by all 课程代号 having 课程代号 is not null 实现的过程如图 9 所示。图 9 使用 ALL 关键字分组查询成绩表 5在分组查询中使用 CUBE 运算符 CUBE 运算符的主要作用是自动对 GROUP BY 子句中列出的字段进展分组汇总运算。CUBE 运算符生成的结果集是多维数据集。多维数据集是事实数据的扩展，事实数据即记录个别事件的数据。扩展建立在用户打算分析的列上，这些列被称为维。多维数据集是一个结果集，其中包含了各维度的所有可能组合的穿插表格。CUBE 运算符在 SQL 语句的 GROUP BY 子句中指定。该语句的选择列表应包含维度列和聚合函数表达式。GROUP BY 应指定维度列和关键字 WITH CUBE。结果集将包含维度列中各值的所有可能组合，以及与这些维度值组合相匹配的根底行中的聚集值。下面举一个简单的例子，一个简单的表 Inventory，其内容如表 1 所示：表 1 Inventory 表构造 Item Color Quantity Table Blue 124 Table Red 223 Chair Blue 101 Chair Red 210 在查询分析器中输入的 SQL 语句如下：SELECT Item,Color,SUM(Quantity)AS QtySum FROM Inventory GROUP BY Item,Color WITH CUBE 其查询结果如表 2 所示：表 2 查询结果集 Item Color QtySum Chair Blue Chair Red Chair NULL Table Blue Table Red Table NULL NULL NULL NULL Blue NULL Red 下面着重考察以下各行,如表 3、表 4、表 5 和表 6 所示。表 3 记录行 1 Chair NULL 这一行显示了 Item 维度中值为 Chair 的所有行的小计。对 Color 维度返回了 NULL 值，表示该行所显示的聚集包括 Color 维度为任意值的行。表 4 记录行 2 Table NULL 这一行类似，但显示的是 Item 维度中值为 Table 的所有行的小计。表 5 记录行 3 NULL NULL 这一行显示了多维数据集的总计。Item 和 Color 维度的值都是 NULL，表示两个维度中的所有值都汇总在该行中。表 6 记录行 4 NULL Blue NULL Red 这两行显示了 Color 维度的小计。两行中的 Item 维度值都是 NULL，表示聚集数据来自Item 维度为任意值的行。例如：在“grade表中，按“学期和“课程代号分组求课程的平均成绩，并且用 CUBE运算符进展小计。在查询分析器中输入的 SQL 语句如下：use student select 学期,课程代号,avg(课程成绩)as 平均成绩 from grade group by 学期,课程代号 with cube having 课程代号 is not null 实现的过程如图 10 所示。图 10 对“grade表统计小计 6在分组查询中使用 ROLLUP 在使用 GROUP BY 生成包含小计和合计的报表时，ROLLUP 运算符很有用。ROLLUP运算符生成的结果集类似于 CUBE 运算符所生成的结果集。CUBE 和 ROLLUP 之间的区别在于：1CUBE 生成的结果集显示了所选列中值的所有组合的聚集。2ROLLUP 生成的结果集显示了所选列中值的某一层次构造的聚集。下面同样以一个简单表 Inventory 为例来介绍如何使用 ROLLUP 运算符，如表 7 所示。表 7 Inventory 表 Item Color Quantity Table Blue 124 Table Red 223 Chair Blue 101 Chair Red 210 在查询分析器中输入的 SQL 语句如下：SELECT CASE WHEN(GROUPING(Item)=1)THEN ALL ELSE ISNULL(Item,UNKNOWN)END AS Item,CASE WHEN(GROUPING(Color)=1)THEN ALL ELSE ISNULL(Color,UNKNOWN)END AS Color,SUM(Quantity)AS QtySum FROM Inventory GROUP BY Item,Color WITH ROLLUP 其执行结果集如表 7 所示。假设查询中的 ROLLUP 关键字更改为 CUBE，那么 CUBE 结果集与上述结果一样，只是在结果集的末尾还会返回以下两行，如表 8 所示。表 8 执行结果集 1 Item Color QtySum Chair Blue Chair Red Chair ALL Table Blue Table Red Table ALL ALL ALL 表 9 执行结果集 2 ALL Blue ALL Red CUBE 操作为 Item 和 Color 中值的可能组合生成行。例如，CUBE 不仅报告与 Item 值Chair 相组合的 Color 值的所有可能组合 Red、Blue 和 Red+Blue，而且报告与 Color 值 Red相组合的 Item 值的所有可能组合Chair、Table 和 Chair+Table。对于 GROUP BY 子句中右边的列中的每个值，ROLLUP 操作并不报告左边一列或左边各列中值的所有可能组合。例如，ROLLUP 并不对每个 Color 值报告 Item 的所有可能组合。ROLLUP操作的结果集具有类似于COMPUTE BY所返回结果集的功能，然而，ROLLUP具有以下优点：1ROLLUP 返回单个结果集；COMPUTE BY 返回多个结果集，而多个结果集会增加应用程序代码的复杂性。2ROLLUP 可以在效劳器游标中使用；COMPUTE BY 不可以。3 有时查询优化器为 ROLLUP 生成的执行方案比为 COMPUTE BY 生成的更为高效。注意：WITH ROLLUP 关键字主要对 GROUP BY 子句中列出的第一个分组字段进展汇总计算。GROUP BY 子句假设有两个字段，那么字段位置不同，返回的结果集也不同。例如：在“grade表中，按“学期和“课程代号分组求课程的平均成绩，并且用 CUBE运算符进展小计。在查询分析器中输入的 SQL 语句如下：use student select 学期,课程代号,avg(课程成绩)as 平均成绩 from grade group by 学期,课程代号 with rollup having 课程代号 is not null 实现的过程如图 11 所示。图 11 对成绩表统计并合计