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课程代号1课程成统1学期1B005KLI2 95.22B0LI3k03 98.31_<NULL><UULL><1WLL><miLL>2\1总成绩_ELI03k03 S8.31L,/1 556.80...BLILI4k04 39.92 )B002k02 92.42佥询后》_BOLI1KLU 92.71询前》图1求课程成绩的总和当与GROUPBY子句一起使用时，每个聚集函数都为每一组生成一个值，而不是对整个表生成一个值。示例：在“student”表中，按“性别”分别求年龄的平均值。在查询分析器中输入&01语句如下：usestudentselect性别，avg(年龄)as平均年龄fromstudentgroupby，性另U实现的过程如图2所示。

学号I姓名I性别 I年龄T出生日期 I联系方式I男男女女女女伟葱葱白大导月小葱小刘李刘李李李361245_un-n-_u_u-UoooooOEBbBEBTOC\o"1-5"\h\z1986-01-01 13451男男女女女女伟葱葱白大导月小葱小刘李刘李李李361245_un-n-_u_u-UoooooOEBbBEB性别平均年龄Q1男...2012女...22詹询后》1986-01-03 234511985-03-03 523451984-03-10 6234522 1984-03-10 2345124 1982-03-03 82345建询前〉图2男女生的平均年龄2.MIN()函数和MAX()函数MIN()和MAX()函数分别查询列中的最小值和最大值。但列的数据包含数字、字符或日期/时间信息。MIN()和MAX()函数结果与列中数据的数据类型完全相同。示例：查询“student”表中最早出生的学生。在查询分析器中输入的SQL语句如下：usestudentselectmin（出生日期）as最早出生fromStudent实现的过程结果如图3所示。图3学生表中年龄最小的学生信息下面把GROUPBY子句和MAX()函数结合使用。示例：在“student”表中，按“性别”分别求年龄的最大值。在查询分析器中输入&01语句如下：usestudentselect性别,max(年龄)as最大年龄fromStudentgroupby性别实现的过程如图4所示。学号|姓名|性别 |年龄T出生日期 |联系方式B0LI3刘大伟20 1986-01-0161245ooooooooooETDEEB男女B0LI3刘大伟20 1986-01-0161245ooooooooooETDEEB男女134511986-01-03 234511985-03-03 523451984-03-10 6234522 I'dS^-LiS-lLi 2345124 1902-03-03 S2345性别最大年龄1男...202女...24《查询后＞图4男女生中年龄的最大值3.COUNT()函数和COUNT_big()函数COUNT()函数和COUNT_big()函数两个函数都是对列中数据值的数目进行计数。它们返回的值总是一个整数，不管列的数据类型。示例：求“student”表中女生的人数。usestudentselectcount（年龄）as女生记录总数

fromstudentwhere性别='女’实现的过程如图5所示。学号I姓名I性别I年龄I出生日期 学号I姓名I性别I年龄I出生日期 I联系方式B0LI3刘大伟20 1986-01-01BLILI6bOOlB002£：004B0LI5李导刘月李小葱李葱葱李小白2L2122既241986-01-031985-03-031984-03-101984-03-101982-03-03134512345152345623452345182345苦询前)图5 “Student”表中女生的记录总数COUNT(*)就可以求整个表所有的记录数。例如，求“student”表中所有的记录数，SQL语句如下：usestudentselectfromstudentselectfromstudent4.消除重复记录(DISTINCT)指定DISTINCT关键字不但可以消除查询结果中的重复记录，而且在使用SUM()、AVG()和COUNT()聚合函数时，可以从列中消除重复的值。DISTINCT关键字和聚合函数使用的格式是：聚合函数名称(DISTINCT列名)。示例：在“grade”表中，统计多少学生参加考试。在查询分析器中输入的SQL语句如下：usestudentselect count（学号）from实现的过程如图6所示。学号课程代号1课程成绩学期学号课程代号1课程成绩学期1E005K0295.22E003kH390.31<NULL><NULL><NULL><NULL>B003k3388.31ELI0489.92B00292.42BLILHKLU92.71适询前》(无列名)I16詹询后》图6统计参加考试的学生从上面的统计结果不难看出，实际上参加考试的学生是学号从B001〜B005共5名，其中有重复的学号。这样为了正确统计到底有多少学生参加考试，就必须用到关键字DISTINCTo示例：在“grade”表中，统计多少学生参加考试。在查询分析器中输入的SQL语句如下：usestudentselectcount（distinct学号）from实现的过程如图7所示。图7使用DISTINCT关键字统计参加考试的学生注意：当使用DISTINCT关键字时，聚合函数的参数必须是一个简单的列名。筛选分组结果用GROUPBY可以实现数据分组操作，但有时用户不需要对数据表中所有的数据进行分组，这时就需要使用HAVING子句来筛选分组。示例：在“grade”表中，查询参加同一门课程考试的同学至少两个人的课程成绩总和。在查询分析器中输入的SQL语句如下：usestudentselect课程代号,sum(课程成绩)as课程总成绩fromgradegroupby课程代号havingcount(*)>=2实现的过程如图1所示。图1至少两个人对加同一门考试的课程成绩总和1.SQLSELECT语句的执行顺序下面给出SQLSELECT语句的执行顺序。FROM子句组装来自不同数据源的数据。WHERE子句基于指定的条件对记录行进行筛选。GROUPBY子句将数据划分为多个分组。使用聚集函数进行计算。使用HAVING子句筛选分组。计算所有的表达式。使用ORDERBY对结果集进行排序。示例：在“grade”表中，把“学号”内容不为空的记录按照“学号”分组，并且筛选分组结果，选出“课程成绩”大于92的学生信息。在查询分析器中输入的SQL语句如下：usestudentselect学号,avg(课程成绩)as平均成绩fromgrade

where学号isnotnullgroupby学号havingavg（课程成绩）＞92orderby平均成绩实现的过程如图2所示。学号1课程代号学号1课程代号1课程成蜻1学期BLI05KLI295.22BLI03kON98.31<NULL><HULL><hull><NULL>BLI03ldJ388.31BLI04ldJ489.92BLI02ldJ292.42BLIO1KLU92.71爸询前》学号平均成绩1B00292.40...2B00192.70...3BOOS93.29...4B00595.20...笛询后》图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图3求“grade”表中按“学期”分组的平均成绩示例：在“grade”表中，按“课程类别”分组，并且查询“课程类别”不是“计算机类”的课程信息。在查询分析器中输入的SQL语句如下：usestudentselect课程类别fromcoursegroupby课程类别having课程类别＜＞'计算机类'实现的过程如图4所示。课程代号1课程名称 1课程类别1课程内容1k01 个人单曲 艺术类 D3VIHG I 课程类别M2 喜爱的专辑 艺术类 D.5公里 J 1芦术类M3 SQLServer2000计耸机类 查询 「＞Q 秦MJ4 经典金曲 芝术类 童年 —娱亦突k05 个入单曲 娱乐类 冬天快乐 詹询后〉数据结构 计算机类二叉树适询前》图4 “grade”表按“课程类别”分组统计3.比较HAVING子句与WHERE子句两个子句的相似之处。它们都是从结果表中筛选数据。它们都设置了某些数据能通过而其他数据不能通过的条件。两个子句的不同之处。WHERE子句可以在进行任何处理之前从原表、原始数据中筛选行。HAVING子句可以在进行绝大部分处理之后筛选已分组和已总结的数据。WHERE子句不能在它设置的条件之中使用列函数。HAVING子句可以在它的条件中使用列函数。理解HVING子句的最好方法就是记住SELECT语句中的哪些子句是按照明确的次序进行处理的。WHERE子句只能接收来自FROM子句的输入，而HAVING子句则可以接收来自GROUPBY、WHERE子句或FROM子句的输入。这是一个微妙但却重要的差别。示例：在“grade”表中，把“课程成绩”大于92分的按“学期”分组求平均成绩。在查询分析器中输入的SQL语句如下：usestudentselect学期,avg(课程成绩)as平均成绩fromgradewhere课程成绩＞92groupby学期实现的过程如图5所示。学号 1课程代号1课程成绩1学期E0LI5 K02 95.2 2B003 k03 98.3 1学期平均成绩<1IULL><NULL><NULL><NULL>厂T' 1 1 95.5B003 k03 88.3 I/2 2 93.80E0U4 k04 89.9 2hz——B002 kOH 92.4 2詹询后》B001 KOI 92.7 1适询前〉图5按学期求大于92分的课程的平均成绩上个例子，首先挑选出“课程成绩”大于2分的学生信息，然后按“学期”再分组求课程成绩的平均值。下面把WHERE子句替换成HAVING子句，在查询分析器中运行的结果如图6所示。

图6图6按“学期”分组用HAVING子句设置条件SQL语句如下所示：usestudentselect学期,avg（课程成绩）as平均成绩fromgradegroupby学期having课程成绩＞92由此可见，执行用HAVING子句替换WHERE子句的语句是错误的。因为“课程成绩”列既不包含在聚合函数中，也不包含在GROUPBY子句中。下面改变HAVING子句的条件，这个子句包括一个用了聚合函数的列。示例：在“grade”表中，按“学期”求课程成绩的平均值，并筛选出平均成绩大于92分的。在查询分析器中输入的SQL语句如下：usestudentselect学期,avg（课程成绩）as平均成绩fromgradegroupby学期havingavg（课程成绩）＞92实现的过程如图7所示。学号1课程代号学号1课程代号1课程成绩1学期B0LI5K0295.2|2B003k0398.31<null><NULL><NULL><NULL>B003ld：i388.31B004k0489.92B002ldJ292.42BLILI1B：LH,32.71詹询前》学期平均或绩1 93.10...2 92.5詹询后》图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图8分组查询成绩表在上面的例子中使用ALL关键字。示例：在“grade”表中，按“课程代号”分组求出课程的平均成绩，并不显示“课程成绩”的值为NULL值。在查询分析器中输入的SQL语句如下：usestudentselect课程代号,avg（课程成绩）as平均成绩fromgradewhere学号!='b003'groupbyall课程代号having课程代号isnotnull实现的过程如图9所示。学号课程代号1学号课程代号1课程成绩1学期1B0LI5K0295.22E003k：i398.31<null><null><null><NULL>B003kJ388.31E004k：i4S9.92B002k0292.42EOLI1KOI92.71詹询前》课程代号平均成绩1KOI92.70...2k0293.80...3k03NULL4k0489.90...佥询后》图9使用ALL关键字分组查询成绩表5.在分组查询中使用CUBE运算符CUBE运算符的主要作用是自动对GROUPBY子句中列出的字段进行分组汇总运算。CUBE运算符生成的结果集是多维数据集。多维数据集是事实数据的扩展，事实数据即记录个别事件的数据。扩展建立在用户打算分析的列上，这些列被称为维。多维数据集是一个结果集，其中包含了各维度的所有可能组合的交叉表格。CUBE运算符在SQL语句的GROUPBY子句中指定。该语句的选择列表应包含维度列和聚合函数表达式。GROUPBY应指定维度列和关键字WITHCUBE。结果集将包含维度列中各值的所有可能组合，以及与这些维度值组合相匹配的基础行中的聚集值。下面举一个简单的例子，一个简单的表Inventory，其内容如表1所示：

表1Inventory表结构ItemColorQuantityTableBlue124TableRed223ChairBlue101ChairRed210在查询分析器中输入的SQL语句如下：SELECTItem,Color,SUM(Quantity)ASQtySumFROMInventoryGROUPBYItem,ColorWITHCUBE其查询结果如表2所示：表2查询结果集ItemColorQtySumChairBlue101.00ChairRed210.00ChairNULL311.00TableBlue124.00TableRed223.00TableNULL347.00NULLNULL658.00NULLBlue225.00NULLRed433.00下面着重考查下列各行，如表3、表4、表5和表6所示。表3记录行1Chair NULL 311.00这一行显示了Item维度中值为Chair的所有行的小计。对Color维度返回了NULL值，表示该行所显示的聚集包括Color维度为任意值的行。表4记录行2Table NULL 347.00这一行类似，但显示的是Item维度中值为Table的所有行的小计。表5记录行3NULL NULL 658.00这一行显示了多维数据集的总计。Item和Color维度的值都是NULL，表示两个维度中的所有值都汇总在该行中。表6记录行4NULLBlue225.00NULLRed433.00这两行显示了Color维度的小计。两行中的Item维度值都是NULL,表示聚集数据来自Item维度为任意值的行。示例：在“grade”表中，按“学期”和“课程代号”分组求课程的平均成绩，并且用CUBE运算符进行小计。在查询分析器中输入的SQL语句如下：usestudentselect学期，课程代号,avg(课程成绩)as平均成绩fromgradegroupby学期，课程代号withcubehaving课程代号isnotnull实现的过程如图10所示。学号1课程代号1课程成绩1学期学期课程代号平均成绩_B005K0295.2211KOI92.70..._B003ld：i398.312J1 k0393.29..._<1IULL><1IULL><1IULL><1IULL> 」~2 k0293.80...B003ld：i388.31[>T~k0489.9 L/J2k0489.90...BUU4I-—B002ld：i292.42_NULLKOI92.70...B001KOI92.716_NULLk0293.80...詹询前》7_NULLkO393.29...8jNULLkO489.90...詹询后》图10对“grade”表统计小计6.在分组查询中使用ROLLUP在使用GROUPBY生成包含小计和合计的报表时,ROLLUP运算符很有用。ROLLUP运算符生成的结果集类似于CUBE运算符所生成的结果集。CUBE和ROLLUP之间的区别在于：CUBE生成的结果集显示了所选列中值的所有组合的聚集。ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚集。下面同样以一个简单表Inventory为例来介绍如何使用ROLLUP运算符，如表7所示。表7Inventory表ItemColorQuantityTableBlue124TableRed223ChairBlue101ChairRed210在查询分析器中输入的SQL语句如下：SELECTCASEWHEN(GROUPING(Item)=1)THEN'ALL'ELSEISNULL(Item,'UNKNOWN')ENDASItem,CASEWHEN(GROUPING(Color)=1)THEN'ALL'ELSEISNULL(Color,'UNKNOWN')ENDASColor,SUM(Quantity)ASQtySumFROMInventoryGROUPBYItem,ColorWITHROLLUP其执行结果集如表7所示。如果查询中的ROLLUP关键字更改为CUBE，那么CUBE结果集与上述结果相同，只是在结果集的末尾还会返回下列两行，如表8所示。表8执行结果集1ItemColorQtySumChairBlue101.00ChairRed210.00ChairAL