SQL汇总和分组数据

使用聚合函数进行汇总和分组SQL提供一组聚合函数，它们能够对整个数据集合进行计算，将一组原始数据转换为有用的信息，以便用户使用。例如求成绩表中的总成绩、学生表中平均年龄等。SQL的聚合函数如表1所示。表1聚合函数聚合函数支持的数据类型功能描述Sum()数字对指定列中的所有非空值求和avg()数字对指定列中的所有非空值求平均值min()数字、字符、日期返回指定列中的最小数字、最小的字符串和最早的日期时间max()数字、字符、日期返回指定列中的最大数字、最大的字符串和最近的日期时间count（[distinct]*）任意基于行的数据类型统计结果集中全部记录行的数量，最多可达2147483647行count_big（[distinct]*）任意基于行的数据类型类似于count()函数，但因其返回值使用了bigint数据类型，所以最多可以统计2^63-1行1．SUM()函数和AVG()函数两个函数都是对列式数字型的进行计算，只不过SUM()是对列求和；而AVG()是对列求平均值。示例：求“grade”表中每学期的课程成绩的总和。在查询分析器中输入的SQL语句如下：usestudentselectsum(课程成绩)as总成绩fromgrade实现的过程如图1所示。图1求课程成绩的总和当与GROUPBY子句一起使用时，每个聚集函数都为每一组生成一个值，而不是对整个表生成一个值。示例：在“student”表中，按“性别”分别求年龄的平均值。在查询分析器中输入的SQL语句如下：usestudentselect性别,avg(年龄)as平均年龄fromstudentgroupby性别实现的过程如图2所示。图2男女生的平均年龄2．MIN()函数和MAX()函数MIN()和MAX()函数分别查询列中的最小值和最大值。但列的数据包含数字、字符或日期/时间信息。MIN()和MAX()函数结果与列中数据的数据类型完全相同。示例：查询“student”表中最早出生的学生。在查询分析器中输入的SQL语句如下：usestudentselectmin(出生日期)as最早出生fromStudent实现的过程结果如图3所示。图3学生表中年龄最小的学生信息下面把GROUPBY子句和MAX()函数结合使用。示例：在“student”表中，按“性别”分别求年龄的最大值。在查询分析器中输入的SQL语句如下：usestudentselect性别,max(年龄)as最大年龄fromStudentgroupby性别实现的过程如图4所示。图4男女生中年龄的最大值3．COUNT()函数和COUNT_big()函数COUNT()函数和COUNT_big()函数两个函数都是对列中数据值的数目进行计数。它们返回的值总是一个整数，不管列的数据类型。示例：求“student”表中女生的人数。在查询分析器中输入的SQL语句如下：usestudentselectcount(年龄)as女生记录总数fromstudentwhere性别='女'实现的过程如图5所示。图5“Student”表中女生的记录总数COUNT（*）就可以求整个表所有的记录数。例如，求“student”表中所有的记录数，SQL语句如下：usestudentselectcount(*)fromstudent4．消除重复记录（DISTINCT）指定DISTINCT关键字不但可以消除查询结果中的重复记录，而且在使用SUM()、AVG()和COUNT()聚合函数时，可以从列中消除重复的值。DISTINCT关键字和聚合函数使用的格式是：聚合函数名称（DISTINCT列名）。示例：在“grade”表中，统计多少学生参加考试。在查询分析器中输入的SQL语句如下：usestudentselectcount(学号)fromgrade实现的过程如图6所示。图6统计参加考试的学生从上面的统计结果不难看出，实际上参加考试的学生是学号从B001～B005共5名，其中有重复的学号。这样为了正确统计到底有多少学生参加考试，就必须用到关键字DISTINCT。示例：在“grade”表中，统计多少学生参加考试。在查询分析器中输入的SQL语句如下：usestudentselectcount(distinct学号)fromgrade实现的过程如图7所示。图7使用DISTINCT关键字统计参加考试的学生注意：当使用DISTINCT关键字时，聚合函数的参数必须是一个简单的列名。筛选分组结果用GROUPBY可以实现数据分组操作，但有时用户不需要对数据表中所有的数据进行分组，这时就需要使用HAVING子句来筛选分组。示例：在“grade”表中，查询参加同一门课程考试的同学至少两个人的课程成绩总和。在查询分析器中输入的SQL语句如下：usestudentselect课程代号,sum(课程成绩)as课程总成绩fromgradegroupby课程代号havingcount(*)=2实现的过程如图1所示。图1至少两个人对加同一门考试的课程成绩总和1．SQLSELECT语句的执行顺序下面给出SQLSELECT语句的执行顺序。（1）FROM子句组装来自不同数据源的数据。（2）WHERE子句基于指定的条件对记录行进行筛选。（3）GROUPBY子句将数据划分为多个分组。（4）使用聚集函数进行计算。（5）使用HAVING子句筛选分组。（6）计算所有的表达式。（7）使用ORDERBY对结果集进行排序。示例：在“grade”表中，把“学号”内容不为空的记录按照“学号”分组，并且筛选分组结果，选出“课程成绩”大于92的学生信息。在查询分析器中输入的SQL语句如下：usestudentselect学号,avg(课程成绩)as平均成绩fromgradewhere学号isnotnullgroupby学号havingavg(课程成绩)92orderby平均成绩实现的过程如图2所示。图2查询统计“student”表下面给出上个示例中SQL语句的执行顺序。（1）首先执行FROM子句，从“grade”表组装数据源的数据。（2）执行WHERE子句，筛选“grade”表中所有数据不为NULL的数据。（3）执行GROUPBY子句，把“grade”表按“学号”列进行分组。（4）计算AVG()聚集函数，按“课程成绩”求出平均成绩的具体数值。（5）执行HAVING子句，筛选课程的平均成绩大于92分的学生信息。（6）执行ORDERBY子句，把最后的结果按“平均成绩”进行排序。2．HAVING子句在分组搜索条件上的限制HAVING子句指定的搜索条件必须是作为一个整体应用于组而不是应用于各个记录。所以HAVING的搜索条件是有限制的，列举如下：一个常量。一个聚合函数，这个聚合函数生成一个值，该值汇总组中的记录。一个分组列，按照定义，这个分组字段在这个组的每一记录中有同样的值。一个包含上述各项组合的表达式。示例：在“grade”表中，按“学期”分组，求“学期”值不为空的课程成绩平均值。在查询分析器中输入的SQL语句如下：usestudentselectavg(课程成绩)as平均成绩fromgradegroupby学期having学期isnotnull实现的过程如图3所示。图3求“grade”表中按“学期”分组的平均成绩示例：在“grade”表中，按“课程类别”分组，并且查询“课程类别”不是“计算机类”的课程信息。在查询分析器中输入的SQL语句如下：usestudentselect课程类别fromcoursegroupby课程类别having课程类别'计算机类'实现的过程如图4所示。图4“grade”表按“课程类别”分组统计3．比较HAVING子句与WHERE子句两个子句的相似之处。（1）它们都是从结果表中筛选数据。（2）它们都设置了某些数据能通过而其他数据不能通过的条件。两个子句的不同之处。（1）WHERE子句可以在进行任何处理之前从原表、原始数据中筛选行。（2）HAVING子句可以在进行绝大部分处理之后筛选已分组和已总结的数据。（3）WHERE子句不能在它设置的条件之中使用列函数。（4）HAVING子句可以在它的条件中使用列函数。理解HVING子句的最好方法就是记住SELECT语句中的哪些子句是按照明确的次序进行处理的。WHERE子句只能接收来自FROM子句的输入，而HAVING子句则可以接收来自GROUPBY、WHERE子句或FROM子句的输入。这是一个微妙但却重要的差别。示例：在“grade”表中，把“课程成绩”大于92分的按“学期”分组求平均成绩。在查询分析器中输入的SQL语句如下：usestudentselect学期,avg(课程成绩)as平均成绩fromgradewhere课程成绩92groupby学期实现的过程如图5所示。图5按学期求大于92分的课程的平均成绩上个例子，首先挑选出“课程成绩”大于92分的学生信息，然后按“学期”再分组求课程成绩的平均值。下面把WHERE子句替换成HAVING子句，在查询分析器中运行的结果如图6所示。图6按“学期”分组用HAVING子句设置条件SQL语句如下所示：usestudentselect学期,avg(课程成绩)as平均成绩fromgradegroupby学期having课程成绩92由此可见，执行用HAVING子句替换WHERE子句的语句是错误的。因为“课程成绩”列既不包含在聚合函数中，也不包含在GROUPBY子句中。下面改变HAVING子句的条件，这个子句包括一个用了聚合函数的列。示例：在“grade”表中，按“学期”求课程成绩的平均值，并筛选出平均成绩大于92分的。在查询分析器中输入的SQL语句如下：usestudentselect学期,avg(课程成绩)as平均成绩fromgradegroupby学期havingavg(课程成绩)92实现的过程如图7所示。图7按“学期”求成绩的平均值并用HAVING进行筛选当按“学期”分完组后，HAVING子句就应用于这些结果。对于每一个组来说，都要求成绩的平均值，但只有平均成绩大于92分才能包括在结果中。HAVING对分组后的数据可以进行筛选，并且可以使用AVG（）或SUM（）之类的设置功能，而这些是在WHERE子句中无法使用的。4．使用ALL关键字在GROUPBY子句中使用ALL关键字。只有在SQL语句还包括WHERE子句时，ALL关键字才有意义。如果使用ALL关键字，那么查询结果将包括由GROUPBY子句产生的所有组，即使某些组没有符合查询条件的行。没有ALL关键字，包含GROUPBY子句的SELECT语句将不显示没有符合条件的行的组。示例：在“grade”表中，按“课程代号”分组求出课程的平均成绩，并不显示“课程成绩”的值为NULL值的行。在查询分析器中输入的SQL语句如下：usestudentselect课程代号,avg(课程成绩)as平均成绩fromgradewhere学号!='b003'groupby课程代号having课程代号isnotnull实现的过程如图8所示。图8分组查询成绩表在上面的例子中使用ALL关键字。示例：在“grade”表中，按“课程代号”分组求出课程的平均成绩，并不显示“课程成绩”的值为NULL值。在查询分析器中输入的SQL语句如下：usestudentselect课程代号,avg(课程成绩)as平均成绩fromgradewhere学号!='b003'groupbyall课程代号having课程代号isnotnull实现的过程如图9所示。图9使用ALL关键字分组查询成绩表5．在分组查询中使用CUBE运算符CUBE运算符的主要作用是自动对GROUPBY子句中列出的字段进行分组汇总运算。CUBE运算符生成的结果集是多维数据集。多维数据集是事实数据的扩展，事实数据即记录个别事件的数据。扩展建立在用户打算分析的列上，这些列被称为维。多维数据集是一个结果集，其中包含了各维度的所有可能组合的交叉表格。CUBE运算符在SQL语句的GROUPBY子句中指定。该语句的选择列表应包含维度列