精通MS-ACCESS SQL之基础篇（13）

时　间:2017-12-04 11:36:37
作　者:Big Young   ID：252  城市：襄阳
摘　要:    使用ACCESS SQL的交叉表查询。
正　文:

第十三节 使用ACCESS SQL的交叉表查询

交叉表查询是将来源于某个表中的字段进行分组，一组列在交叉表左侧，一组列在交叉表上部，并在交叉表行与列交叉处显示表中某个字段的各种计算值，如计算数据的总和，平均值，计数及其他类型的统计。

交叉表分两种：静态交叉表，即列数固定的交叉表和动态交叉表，即列数依据表中的行数不同而产生不等的列的交叉表。使用交叉表查询来计算和重构数据，可以简化数据分析。

一、交叉表查询的SQL语法

MS ACCESS的交叉表查询在SQL中以TRANSFORM语句来表示。TRANSFORM语句包含以下语法：

TRANSFORM aggfunction

selectstatement

PIVOT pivotfield [IN (value1[, value2[, ...]])]

TRANSFORM语句包含以下部分：

    “TRANSFORM aggfunction”子句中，合计函数计算出的数据将显示于“行标题”与“列标题”交叉所对应的单元格中；“selectstatement”所代表的“Select语句”的子句中所列的各字段项目将作为“行标题”，最多可以选三项，为了表格的简洁一般只选一列；而“PIVOT pivotfield [IN (value1[, value2[, ...]])]”子句部分用于确定查询最终结果表中所显示的“列标题”。

二、静态交叉表查询

前面说过静态交叉表就是列数固定的交叉表，下面我们用具体的示例来说明其如何实现。

例如，我们有一张“成绩”表，记录是学生的各科考试成绩，如图13-1所示：

图13-1：记录学生成绩的表示例

现在我们要按各个科目统计学生的成绩及总分，科目是六个，包括语文、数学、英语、物理、化学、生物，如图13-2所示：

图13-2：按科目及总分统计每个学生的成绩

要实现这样的效果，只要使用如下的查询语句就可以完成：

TRANSFORM SUM(分数)

Select 姓名, SUM(分数) AS 总分

FROM 成绩

GROUP BY 姓名

PIVOT 科目

IN (语文, 数学, 英语, 物理, 化学, 生物);

当前情况下，表中并没有记录物理、化学及生物的成绩，所以对应位置是空的，即为NULL值。

三、动态交叉表查询

所谓动态交叉表，就是列数依据表中的行数不同而产生不等的列的交叉表，我们可以用如下SQL查询语句来达到这种效果：

TRANSFORM SUM(分数)

Select 姓名, SUM(分数) AS 总分

FROM 成绩

GROUP BY 姓名

PIVOT 科目;

我们会发现就是查询语句中没有了指定固定标题的IN子句了，这时列标题会自动根据PIVOT子句后的字段名来生成，数据表中有多少类就会自动生成多少个，如图12-3所示：

图13-3：动态交叉表查询结果示例

总之，交叉表查询类似于EXCEL电子表格中的交叉分析，将数据以行、列二维方式显示，适用于计算总计、平均值、计数或其他类型的总和等统计结果。使用交叉表查询可以计算并重新组织数据的结构，更加方便地分析数据。