您的位置：　首页 > 技术文章 > Access数据库-查询/SQL语句

全面掌握MS ACCESS SQL（35）

来源:网络 点击数:4256 　 | 收藏 | 复制

时　间:2018-01-19 17:31:08
作　者:Big Young ID：252 城市：襄阳
摘　要: TRANSFORM语句实例详解。
正　文:

第二节 TRANSFORM语句实例详解

“交叉表”对象是一个网格，用来根据指定的条件返回值，数据显示在压缩行和列中。这种格式易于比较数据并辨别其趋势。它由三个元素组成：

行、列和摘要字段。

“交叉表”中的行沿水平方向延伸（从一侧到另一侧）。

“交叉表”中的列沿垂直方向延伸（上下）。

汇总字段位于行和列的交叉处。每个交叉处的值代表对既满足行条件又满足列条件的记录的汇总（求和、计数等）。

在说明如何用TRANSFORM语句来创建交叉表前我们还是先准备好示例数据。我们先创建一个交叉表查询示例数据库，在库中创建一个示例数据表，表名为“成绩”，内容如下：

姓名科目成绩

张三语文 99

张三数学 86

张三英语 75

李四语文 78

李四数学 85

李四英语 78

创建此表的相关SQL语句如下：

Create TABLE 成绩 (姓名 char(10), 科目 char(10), 成绩 int);

Insert INTO 成绩 (姓名,科目,成绩) VALUES ('张三','语文',99);

Insert INTO 成绩 (姓名, 科目, 成绩) VALUES ('张三','数学',86);

Insert INTO 成绩 (姓名, 科目, 成绩) VALUES ('张三','英语',75);

Insert INTO 成绩 (姓名, 科目, 成绩) VALUES ('李四','语文',78);

Insert INTO 成绩 (姓名, 科目, 成绩) VALUES ('李四','数学',85);

Insert INTO 成绩 (姓名, 科目, 成绩) VALUES ('李四','英语',78);

一、行列转换类型的交叉表

利用上面的数据表，我们想转换成如下的表格式：

姓名语文数学英语

李四 78 85 78

张三 99 86 75

这其实就是一静态的交叉表，也就是说是一个列数固定的交叉表，这种情况其实只要一句简单的Select查询就可以搞定：

Select 姓名,SUM(IIF(a.科目='语文',成绩,NULL)) AS 语文,

SUM(IIF(a.科目='数学',成绩,NULL)) AS 数学,

SUM(IIF(a.科目='英语',成绩,NULL)) AS 英语

FROM 成绩 AS a

GROUP BY 姓名;

当要增加“总计”列：“总分”时，如下表所示：

姓名语文数学英语总分

李四 78 85 78 241

张三 99 86 75 260

只需增加SUM(a.成绩) AS 总分，完整的SQL语句如下：

Select 姓名,SUM(IIF(a.科目='语文',成绩,NULL)) AS 语文,

SUM(IIF(a.科目='数学',成绩,NULL)) AS 数学,

SUM(IIF(a.科目='英语',成绩,NULL)) AS 英语,

SUM(a.成绩) AS 总分

FROM 成绩 AS a

GROUP BY 姓名;

其中利用了VBA函数IIF判断，如果是相应的列，则取需要统计的“成绩”的数值，否则取NULL，然后再合计。

这里有一点需要着重说明一下：此处用了NULL值而不是用0。这样安排是有道理的，假如用0，虽然求和函数SUM可以取到正确的数，但类似COUNT函数（取记录个数），结果就不对了，因为NULL不算一条记录，而0要算，同理空字串（""）也是这样，总之在这里应该用NULL,这样任何函数都没问题。

二、利用ACCESS SQL的TRANSFORM语句来实现行列转换

TRANSFORM SUM(成绩)

Select 姓名

FROM 成绩

GROUP BY 姓名

PIVOT 科目 IN (语文,数学,英语);

运行后的结果与前面的效果一样，如：

姓名语文数学英语

李四 78 85 78

张三 99 86 75

上面的例子是列固定的，所以称为静态交叉表查询，我们也可以写成动态的交叉表查询，就是列会根据表中的数据自动分类确定列数的交叉表，如下所示：

姓名科目分数

张三语文 99

张三数学 86

张三英语 75

李四语文 78

李四数学 85

李四英语 78

王五语文 92

王五数学 90

王五英语 89

张三政治 97

张三历史 98

张三地理 80

李四政治 85

李四历史 99

李四地理 88

王五政治 94

王五历史 92

王五地理 96

TRANSFORM Sum(成绩.分数) AS 分数之合计

Select 成绩.姓名, Sum(成绩.分数) AS 总分

FROM 成绩

GROUP BY 成绩.姓名

ORDER BY 姓名 DESC

PIVOT 成绩.科目;

姓名总分地理历史数学英语语文政治

张三 535 80 98 86 75 99 97

王五 553 96 92 90 89 92 94

李四 513 88 99 85 78 78 85

三、交叉表查询完整示例

行列转换并不是TRANSFORM语句的主要功能，其主要功能是创建交叉表查询，加强对数据的分析能力。交叉表查询可以“重构”汇总数据，使其更容易阅读和理解，它可以在一个查询中提供比汇总查询更多的汇总数据。交叉表查询计算总和、平均值、计数等，然后按照两组值对结果进行分组：一组值垂直分布在数据表的一侧，而另一组值水平分布在数据表的顶端。

比如一个存有销售合同记录的数据表，用交叉表查询可以一次汇总出每月或每季度的销售签订数据，而用汇总查询一般一次只能汇总一个月、一个季度的销售合同数据。

合同号登记日期登记人签订日期签订部门签订人合同名称客户合同额合同份数合同负责人备注