SQL汇总和分组数据.docx

SQL汇总和分组数据.docx

《SQL汇总和分组数据.docx》由会员分享，可在线阅读，更多相关《SQL汇总和分组数据.docx（19页珍藏版）》请在冰豆网上搜索。

SQL汇总和分组数据

使用聚合函数进行汇总和分组

SQL提供一组聚合函数，它们能够对整个数据集合进行计算，将一组原始数据转换为有用的信息，以便用户使用。

例如求成绩表中的总成绩、学生表中平均年龄等。

SQL的聚合函数如表1所示。

表1聚合函数

聚合函数

支持的数据类型

功能描述

Sum（ ）

数字

对指定列中的所有非空值求和

avg（ ）

数字

对指定列中的所有非空值求平均值

min（ ）

数字、字符、日期

返回指定列中的最小数字、最小的字符串和最早的日期时间

max（ ）

数字、字符、日期

返回指定列中的最大数字、最大的字符串和最近的日期时间

count（[distinct]*）

任意基于行的数据类型

统计结果集中全部记录行的数量，最多可达2 147 483 647行

count_big（[distinct]*）

任意基于行的数据类型

类似于count（ ）函数，但因其返回值使用了bigint数据类型，所以最多可以统计2^63-1行

1．SUM（ ）函数和AVG（ ）函数

两个函数都是对列式数字型的进行计算，只不过SUM（ ）是对列求和；而AVG（ ）是对列求平均值。

示例：

求“grade”表中每学期的课程成绩的总和。

在查询分析器中输入的SQL语句如下：

usestudent

selectsum（课程成绩）as总成绩

fromgrade

实现的过程如图1所示。

图1求课程成绩的总和

当与GROUPBY子句一起使用时，每个聚集函数都为每一组生成一个值，而不是对整个表生成一个值。

示例：

在“student”表中，按“性别”分别求年龄的平均值。

在查询分析器中输入的SQL语句如下：

usestudent

select性别,avg（年龄）as平均年龄

fromstudentgroupby性别

实现的过程如图2所示。

图2男女生的平均年龄

2．MIN（ ）函数和MAX（ ）函数

MIN（ ）和MAX（ ）函数分别查询列中的最小值和最大值。

但列的数据包含数字、字符或日期/时间信息。

MIN（ ）和MAX（ ）函数结果与列中数据的数据类型完全相同。

示例：

查询“student”表中最早出生的学生。

在查询分析器中输入的SQL语句如下：

usestudent

selectmin（出生日期）as最早出生

fromStudent

实现的过程结果如图3所示。

图3学生表中年龄最小的学生信息

下面把GROUPBY子句和MAX（ ）函数结合使用。

示例：

在“student”表中，按“性别”分别求年龄的最大值。

在查询分析器中输入的SQL语句如下：

usestudent

select性别,max（年龄）as最大年龄

fromStudent

groupby性别

实现的过程如图4所示。

图4男女生中年龄的最大值

3．COUNT（ ）函数和COUNT_big（ ）函数

COUNT（ ）函数和COUNT_big（ ）函数两个函数都是对列中数据值的数目进行计数。

它们返回的值总是一个整数，不管列的数据类型。

示例：

求“student”表中女生的人数。

在查询分析器中输入的SQL语句如下：

usestudent

selectcount（年龄）as女生记录总数

fromstudent

where性别='女'

实现的过程如图5所示。

图5“Student”表中女生的记录总数

COUNT（*）就可以求整个表所有的记录数。

例如，求“student”表中所有的记录数，SQL语句如下：

usestudent

selectcount（*）fromstudent

4．消除重复记录（DISTINCT）

指定DISTINCT关键字不但可以消除查询结果中的重复记录，而且在使用SUM（ ）、AVG（ ）和COUNT（ ）聚合函数时，可以从列中消除重复的值。

DISTINCT关键字和聚合函数使用的格式是：

聚合函数名称（DISTINCT列名）。

示例：

在“grade”表中，统计多少学生参加考试。

在查询分析器中输入的SQL语句如下：

usestudent

selectcount（学号）

fromgrade

实现的过程如图6所示。

图6统计参加考试的学生

从上面的统计结果不难看出，实际上参加考试的学生是学号从B001～B005共5名，其中有重复的学号。

这样为了正确统计到底有多少学生参加考试，就必须用到关键字DISTINCT。

示例：

在“grade”表中，统计多少学生参加考试。

在查询分析器中输入的SQL语句如下：

usestudent

selectcount（distinct学号）

fromgrade

实现的过程如图7所示。

图7使用DISTINCT关键字统计参加考试的学生

注意：

当使用DISTINCT关键字时，聚合函数的参数必须是一个简单的列名。

筛选分组结果

用GROUPBY可以实现数据分组操作，但有时用户不需要对数据表中所有的数据进行分组，这时就需要使用HAVING子句来筛选分组。

示例：

在“grade”表中，查询参加同一门课程考试的同学至少两个人的课程成绩总和。

在查询分析器中输入的SQL语句如下：

usestudent

select课程代号,sum（课程成绩）as课程总成绩

fromgrade

groupby课程代号

havingcount（*）>=2

实现的过程如图1所示。

图1至少两个人对加同一门考试的课程成绩总和

1．SQLSELECT语句的执行顺序

下面给出SQLSELECT语句的执行顺序。

（1）FROM子句组装来自不同数据源的数据。

（2）WHERE子句基于指定的条件对记录行进行筛选。

（3）GROUPBY子句将数据划分为多个分组。

（4）使用聚集函数进行计算。

（5）使用HAVING子句筛选分组。

（6）计算所有的表达式。

（7）使用ORDERBY对结果集进行排序。

示例：

在“grade”表中，把“学号”内容不为空的记录按照“学号”分组，并且筛选分组结果，选出“课程成绩”大于92的学生信息。

在查询分析器中输入的SQL语句如下：

usestudent

select学号,avg（课程成绩）as平均成绩

fromgrade

where学号isnotnull

groupby学号

havingavg（课程成绩）>92

orderby平均成绩

实现的过程如图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语句如下：

usestudent

selectavg（课程成绩）as平均成绩

fromgrade

groupby学期

having学期isnotnull

实现的过程如图3所示。

图3求“grade”表中按“学期”分组的平均成绩

示例：

在“grade”表中，按“课程类别”分组，并且查询“课程类别”不是“计算机类”的课程信息。

在查询分析器中输入的SQL语句如下：

usestudent

select课程类别

fromcourse

groupby课程类别

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语句如下：

usestudent

select学期,avg（课程成绩）as平均成绩

fromgrade

where课程成绩>92

groupby学期

实现的过程如图5所示。

图5按学期求大于92分的课程的平均成绩

上个例子，首先挑选出“课程成绩”大于92分的学生信息，然后按“学期”再分组求课程成绩的平均值。

下面把WHERE子句替换成HAVING子句，在查询分析器中运行的结果如图6所示。

图6按“学期”分组用HAVING子句设置条件

SQL语句如下所示：

usestudent

select学期,avg（课程成绩）as平均成绩

fromgrade

groupby学期

having课程成绩>92

由此可见，执行用HAVING子句替换WHERE子句的语句是错误的。

因为“课程成绩”列既不包含在聚合函数中，也不包含在GROUPBY子句中。

下面改变HAVING子句的条件，这个子句包括一个用了聚合函数的列。

示例：

在“grade”表中，按“学期”求课程成绩的平均值，并筛选出平均成绩大于92分的。

在查询分析器中输入的SQL语句如下：

usestudent

select学期,avg（课程成绩）as平均成绩

fromgrade

groupby学期

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语句如下：

usestudent

select课程代号,avg（课程成绩）as平均成绩

fromgrade

where学号!

='b003'

groupby课程代号

having课程代号isnotnull

实现的过程如图8所示。

图8分组查询成绩表

在上面的例子中使用ALL关键字。

示例：

在“grade”表中，按“课程代号”分组求出课程的平均成绩，并不显示“课程成绩”的值为NULL值。

在查询分析器中输入的SQL语句如下：

usestudent

select课程代号,avg（课程成绩）as平均成绩

fromgrade

where学号!

='b003'

groupbyall课程代号

having课程代号isnotnull

实现的过程如图9所示。

图9使用ALL关键字分组查询成绩表

5．在分组查询中使用CUBE运算符

CUBE运算符的主要作用是自动对GROUPBY子句中列出的字段进行分组汇总运算。

CUBE运算符生成的结果集是多维数据集。

多维数据集是事实数据的扩展，事实数据即记录个别事件的数据。

扩展建立在用户打算分析的列上，这些列被称为维。

多维数据集是一个结果集，其中包含了各维度的所有可能组合的交叉表格。

CUBE运算符在SQL语句的GROUPBY子句中指定。

该语句的选择列表应包含维度列和聚合函数表达式。

GROUPBY应指定维度列和关键字WITHCUBE。

结果集将包含维度列中各值的所有可能组合，以及与这些维度值组合相匹配的基础行中的聚集值。

下面举一个简单的例子，一个简单的表Inventory，其内容如表1所示：

表1Inventory表结构

Item

Color

Quantity

Table

Blue

124

Table

Red

223

Chair

Blue

101

Chair

Red

210

在查询分析器中输入的SQL语句如下：

SELECTItem,Color,SUM（Quantity）ASQtySum

FROMInventory

GROUPBYItem,ColorWITHCUBE

其查询结果如表2所示：

表2查询结果集

Item

Color

QtySum

Chair

Blue

101.00

Chair

Red

210.00

Chair

NULL

311.00

Table

Blue

124.00

Table

Red

223.00

Table

NULL

347.00

NULL

NULL

658.00

NULL

Blue

225.00

NULL

Red

433.00

下面着重考查下列各行,如表3、表4、表5和表6所示。

表3记录行1

Chair

NULL

311.00

这一行显示了Item维度中值为Chair的所有行的小计。

对Color维度返回了NULL值，表示该行所显示的聚集包括Color维度为任意值的行。

表4记录行2

Table

NULL

347.00

这一行类似，但显示的是Item维度中值为Table的所有行的小计。

表5记录行3

NULL

NULL

658.00

这一行显示了多维数据集的总计。

Item和Color维度的值都是NULL，表示两个维度中的所有值都汇总在该行中。

表6记录行4

NULL

Blue

225.00

NULL

Red

433.00

这两行显示了Color维度的小计。

两行中的Item维度值都是NULL，表示聚集数据来自Item维度为任意值的行。

示例：

在“grade”表中，按“学期”和“课程代号”分组求课程的平均成绩，并且用CUBE运算符进行小计。

在查询分析器中输入的SQL语句如下：

usestudent

select学期,课程代号,avg（课程成绩）as平均成绩

fromgrade

groupby学期,课程代号withcube

having课程代号isnotnull

实现的过程如图10所示。

图10对“grade”表统计小计

6．在分组查询中使用ROLLUP

在使用GROUPBY生成包含小计和合计的报表时，ROLLUP运算符很有用。

ROLLUP运算符生成的结果集类似于CUBE运算符所生成的结果集。

CUBE和ROLLUP之间的区别在于：

（1）CUBE生成的结果集显示了所选列中值的所有组合的聚集。

（2）ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚集。

下面同样以一个简单表Inventory为例来介绍如何使用ROLLUP运算符，如表7所示。

表7Inventory表

Item

Color

Quantity

Table

Blue

124

Table

Red

223

Chair

Blue

101

Chair

Red

210

在查询分析器中输入的SQL语句如下：

SELECTCASEWHEN（GROUPING（Item）=1）THEN'ALL'

ELSEISNULL（Item,'UNKNOWN'）

ENDASItem,

CASEWHEN（GROUPING（Color）=1）THEN'ALL'

ELSEISNULL（Color,'UNKNOWN'）

ENDASColor,

SUM（Quantity）ASQtySum

FROMInventory

GROUPBYItem,ColorWITHROLLUP

其执行结果集如表7所示。

如果查询中的ROLLUP关键字更改为CUBE，那么CUBE结果集与上述结果相同，只是在结果集的末尾还会返回下列两行，如表8所示。

表8执行结果集1

Item

Color

QtySum

Chair

Blue

101.00

Chair

Red

210.00

Chair

ALL

311.00

Table

Blue

124.00

Table

Red

223.00

Table

ALL

347.00

ALL

ALL

658.00

表9执行结果集2

ALL

Blue

225.00

ALL

Red

433.00

CUBE操作为Item和Color中值的可能组合生成行。

例如，CUBE不仅报告与Item值Chair相组合的Color值的所有可能组合（Red、Blue和Red+Blue），而且报告与Color值Red相组合的Item值的所有可能组合（Chair、Table和Chair+Table）。

对于GROUPBY子句中右边的列中的每个值，ROLLUP操作并不报告左边一列（或左边各列）中值的所有可能组合。

例如，ROLLUP并不对每个Color值报告Item的所有可能组合。

ROLLUP操作的结果集具有类似于COMPUTEBY所返回结果集的功能，然而，ROLLUP具有下列优点：

（1）ROLLUP返回单个结果集；COMPUTEBY返回多个结果集，而多个结果集会增加应用程序代码的复杂性。

（2）ROLLUP可以在服务器游标中使用；COMPUTEBY不可以。

（3）有时查询优化器为ROLLUP生成的执行计划比为COMPUTEBY生成的更为高效。

注意：

WITHROLLUP关键字主要对GROUPBY子句中列出的第一个分组字段进行汇总计算。

GROUPBY子句如果有两个字段，那么字段位置不同，返回的结果集也不同。

示例：

在“grade”表中，按“学期”和“课程代号”分组求课程的平均成绩，并且用CUBE运算符进行小计。

在查询分析器中输入的SQL语句如下：

usestudent

select学期,课程代号,avg（课程成绩）as平均成绩

fromgrade

groupby学期,课程代号withrollup

having课程代号isnotnull

实现的过程如图11所示。

图11对成绩表统计并合计