SQL汇总和分组数据.doc

使用聚合函数进行汇总和分组SQL提供一组聚合函数，它们能够对整个数据集合进行计算，将一组原始数据转换为有用的信息，以便用户使用。例如求成绩表中的总成绩、学生表中平均年龄等。SQL的聚合函数如表1所示。表1 聚合函数聚合函数支持的数据类型功 能 描 述Sum()数字对指定列中的所有非空值求和avg()数字对指定列中的所有非空值求平均值min()数字、字符、日期返回指定列中的最小数字、最小的字符串和最早的日期时间max()数字、字符、日期返回指定列中的最大数字、最大的字符串和最近的日期时间count（distinct *）任意基于行的数据类型统计结果集中全部记录行的数量，最多可达2147483647行count_big（distinct *）任意基于行的数据类型类似于count()函数，但因其返回值使用了bigint数据类型，所以最多可以统计263-1行1SUM()函数和AVG()函数两个函数都是对列式数字型的进行计算，只不过SUM()是对列求和；而AVG()是对列求平均值。示例：求“grade”表中每学期的课程成绩的总和。在查询分析器中输入的SQL语句如下：use studentselect sum(课程成绩) as 总成绩 from grade 实现的过程如图1所示。图1 求课程成绩的总和当与GROUP BY子句一起使用时，每个聚集函数都为每一组生成一个值，而不是对整个表生成一个值。示例：在“student”表中，按“性别”分别求年龄的平均值。在查询分析器中输入的SQL语句如下：use studentselect 性别 ,avg(年龄) as 平均年龄 from student group by 性别 实现的过程如图2所示。图2 男女生的平均年龄2MIN()函数和MAX()函数MIN()和MAX()函数分别查询列中的最小值和最大值。但列的数据包含数字、字符或日期/时间信息。MIN()和MAX()函数结果与列中数据的数据类型完全相同。示例：查询“student”表中最早出生的学生。在查询分析器中输入的SQL语句如下：use studentselect min(出生日期) as 最早出生 from Student实现的过程结果如图3所示。图3 学生表中年龄最小的学生信息下面把GROUP BY子句和MAX()函数结合使用。示例：在“student”表中，按“性别”分别求年龄的最大值。在查询分析器中输入的SQL语句如下：use studentselect 性别, max(年龄) as 最大年龄 from Student group by 性别 实现的过程如图4所示。图4 男女生中年龄的最大值3COUNT()函数和COUNT_big()函数COUNT()函数和COUNT_big()函数两个函数都是对列中数据值的数目进行计数。它们返回的值总是一个整数，不管列的数据类型。示例：求“student”表中女生的人数。在查询分析器中输入的SQL语句如下：use studentselect count(年龄) as 女生记录总数 from student where 性别=女实现的过程如图5所示。图5 “Student”表中女生的记录总数COUNT（*）就可以求整个表所有的记录数。例如，求“student”表中所有的记录数，SQL语句如下：use studentselect count(*) from student 4消除重复记录（DISTINCT）指定DISTINCT关键字不但可以消除查询结果中的重复记录，而且在使用SUM()、AVG()和COUNT()聚合函数时，可以从列中消除重复的值。DISTINCT关键字和聚合函数使用的格式是：聚合函数名称（DISTINCT 列名）。示例：在“grade”表中，统计多少学生参加考试。在查询分析器中输入的SQL语句如下：use studentselect count(学号) from grade 实现的过程如图6所示。图6 统计参加考试的学生从上面的统计结果不难看出，实际上参加考试的学生是学号从B001B005共5名，其中有重复的学号。这样为了正确统计到底有多少学生参加考试，就必须用到关键字DISTINCT。示例：在“grade”表中，统计多少学生参加考试。在查询分析器中输入的SQL语句如下：use studentselect count(distinct 学号) from grade 实现的过程如图7所示。图7 使用DISTINCT关键字统计参加考试的学生注意：当使用DISTINCT关键字时，聚合函数的参数必须是一个简单的列名。筛选分组结果用GROUP BY可以实现数据分组操作，但有时用户不需要对数据表中所有的数据进行分组，这时就需要使用HAVING子句来筛选分组。示例：在“grade”表中，查询参加同一门课程考试的同学至少两个人的课程成绩总和。在查询分析器中输入的SQL语句如下：use studentselect 课程代号, sum(课程成绩) as 课程总成绩 from grade group by 课程代号 having count(*)=2 实现的过程如图1所示。图1 至少两个人对加同一门考试的课程成绩总和1SQL SELECT语句的执行顺序下面给出SQL SELECT语句的执行顺序。（1）FROM子句组装来自不同数据源的数据。（2）WHERE子句基于指定的条件对记录行进行筛选。（3）GROUP BY子句将数据划分为多个分组。（4）使用聚集函数进行计算。（5）使用HAVING子句筛选分组。（6）计算所有的表达式。（7）使用ORDER BY对结果集进行排序。示例：在“grade”表中，把“学号”内容不为空的记录按照“学号”分组，并且筛选分组结果，选出“课程成绩”大于92的学生信息。在查询分析器中输入的SQL语句如下：use studentselect 学号,avg(课程成绩) as 平均成绩from gradewhere 学号 is not nullgroup by 学号having avg(课程成绩)92order 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的搜索条件是有限制的，列举如下：l l 一个常量。l l 一个聚合函数，这个聚合函数生成一个值，该值汇总组中的记录。l l 一个分组列，按照定义，这个分组字段在这个组的每一记录中有同样的值。l l 一个包含上述各项组合的表达式。示例：在“grade”表中，按“学期”分组，求“学期”值不为空的课程成绩平均值。在查询分析器中输入的SQL语句如下：use studentselect avg(课程成绩) as 平均成绩 from grade group by 学期 having 学期 is not null实现的过程如图3所示。 图3 求“grade”表中按“学期”分组的平均成绩示例：在“grade”表中，按“课程类别”分组，并且查询“课程类别”不是“计算机类”的课程信息。在查询分析器中输入的SQL语句如下：use studentselect 课程类别from coursegroup by 课程类别having 课程类别计算机类实现的过程如图4所示。图4 “grade”表按“课程类别”分组统计3比较HAVING子句与WHERE子句两个子句的相似之处。（1）它们都是从结果表中筛选数据。（2）它们都设置了某些数据能通过而其他数据不能通过的条件。两个子句的不同之处。（1）WHERE子句可以在进行任何处理之前从原表、原始数据中筛选行。（2）HAVING子句可以在进行绝大部分处理之后筛选已分组和已总结的数据。（3）WHERE子句不能在它设置的条件之中使用列函数。（4）HAVING子句可以在它的条件中使用列函数。理解HVING子句的最好方法就是记住SELECT语句中的哪些子句是按照明确的次序进行处理的。WHERE子句只能接收来自FROM子句的输入，而HAVING子句则可以接收来自GROUP BY、WHERE子句或FROM子句的输入。这是一个微妙但却重要的差别。示例：在“grade”表中，把“课程成绩”大于92分的按“学期”分组求平均成绩。在查询分析器中输入的SQL语句如下：use studentselect 学期,avg(课程成绩) as 平均成绩from gradewhere 课程成绩92group by 学期实现的过程如图5所示。图5 按学期求大于92分的课程的平均成绩上个例子，首先挑选出“课程成绩”大于92分的学生信息，然后按“学期”再分组求课程成绩的平均值。下面把WHERE子句替换成HAVING子句，在查询分析器中运行的结果如图6所示。图6 按“学期”分组用HAVING子句设置条件SQL语句如下所示：use studentselect 学期,avg(课程成绩) as 平均成绩from gradegroup by 学期having 课程成绩92 由此可见，执行用HAVING子句替换WHERE子句的语句是错误的。因为“课程成绩”列既不包含在聚合函数中，也不包含在GROUP BY子句中。下面改变HAVING子句的条件，这个子句包括一个用了聚合函数的列。示例：在“grade”表中，按“学期”求课程成绩的平均值，并筛选出平均成绩大于92分的。在查询分析器中输入的SQL语句如下：use studentselect 学期,avg(课程成绩) as 平均成绩from gradegroup 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 studentselect 课程代号,avg(课程成绩) as 平均成绩from grade where 学号!=b003group by 课程代号 having 课程代号 is not null实现的过程如图8所示。图8 分组查询成绩表在上面的例子中使用ALL关键字。示例：在“grade”表中，按“课程代号”分组求出课程的平均成绩，并不显示“课程成绩”的值为NULL值。在查询分析器中输入的SQL语句如下：use studentselect 课程代号,avg(课程成绩) as 平均成绩from grade where 学号!=b003group 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表结构ItemColorQuantityTableBlue124TableRed223ChairBlue101ChairRed210在查询分析器中输入的SQL语句如下： SELECT Item, Color, SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item, Color WITH CUBE其查询结果如表2所示：表2 查询结果集ItemColorQtySumChairBlue101.00ChairRed210.00ChairNULL311.00TableBlue124.00TableRed223.00TableNULL347.00NULLNULL658.00NULLBlue225.00NULLRed433.00下面着重考查下列各行,如表3、表4、表5和表6所示。表3 记录行1ChairNULL311.00这一行显示了Item维度中值为Chair的所有行的小计。对Color维度返回了NULL值，表示该行所显示的聚集包括Color维度为任意值的行。表4 记录行2TableNULL347.00这一行类似，但显示的是Item维度中值为Table的所有行的小计。表5 记录行3NULLNULL658.00这一行显示了多维数据集的总计。Item和Color维度的值都是NULL，表示两个维度中的所有值都汇总在该行中。表6 记录行4NULLBlue225.00NULLRed433.00这两行显示了Color维度的小计。两行中的Item维度值都是NULL，表示聚集数据来自Item维度为任意值的行。示例：在“grade”表中，按“学期”和“课程代号”分组求课程的平均成绩，并且用CUBE运算符进行小计。在查询分析器中输入的SQL语句如下：use studentselect 学期, 课程代号,avg(课程成绩) as 平均成绩from grade group by 学期,课程代号 with cube having 课程代号 is not null 实现的过程如图10所示。图10 对“grade”表统计小计6在分组查询中使用ROLLUP在使用GROUP BY生成包含小计和合计的报表时，ROLLUP 运算符很有用。ROLLUP运算符生成的结果集类似于CUBE运算符所生成的结果集。CUBE和ROLLUP之间的区别在于：（1）CUBE生成的结果集显示了所选列中值的所有组合的聚集。（2）ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚集。下面同样以一个简单表Inventory为例来介绍如何使用ROLLUP运算符，如表7所示。表7 Inventory表ItemColorQuantityTableBlue124TableRed223ChairBlue101ChairRed210在查询分析器中输入的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 QtySumFROM InventoryGROUP BY Item, Color WITH ROLLUP其执行结果集如表7所示。如果查询中的ROLLUP 关键字更改为CUBE，那么CUBE结果集与上述结果相同，只是在结果集的末尾还会返回下列两行，如表8所示。表8 执行结果集1ItemColorQtySumChairBlue101.00ChairRed210.00ChairALL311.00TableBlue124.00TableRed223.00Ta