交叉表查询中的累计

交叉表查询无疑有使用中可以很方便的进行数据的分析处理。你可以通过向导来生成（在向导中你可以选择是否生成行合计）或者直接按照这个access特有JET-SQL语法来写这个SQL语句。

TRANSFORM合计函数
selectstatement
TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]

比如现有表 table3, 数据如下
+----+---------+-------+--------+------+-------------+
|id  |sName |sClass |Course  |Score |homeworkScore|
+----+---------+-------+--------+------+-------------+
|1 |AAA    |3    |语文 |50 |76          |
|2 |AAA    |3    |数学 |83 |77          |
|3 |AAA    |3    |英语 |65 |60          |
|4 |BBB    |3    |语文 |86 |72          |
|5 |BBB    |3    |数学 |95 |57          |
.......
|31  |LL    |5    |语文 |80 |75          |
|32  |LL    |5    |数学 |95 |70          |
+----+---------+-------+--------+------+-------------+
可以用向导得到一个每人的成绩表如下
+--------+-------+---------------+-------+-------+-------+
|sName |sClass |Total Of Score |数学 |英语 |语文 |
+--------+-------+---------------+-------+-------+-------+
|AAA    |3    |198          |83    |65    |50    |
|BBB    |3    |239          |95    |58    |86    |
......
|LL    |5    |175          |95    |    |80    |
+--------+-------+---------------+-------+-------+-------+

它对应的SQL语句如下：
TRANSFORM Sum(Table3.Score) AS ScoreOfSum
SELECT Table3.sName, Table3.sClass, Sum(Table3.Score) AS [Total Of Score]
FROM Table3
GROUP BY Table3.sName, Table3.sClass
PIVOT Table3.Course;

关于这个SQL语句的说明，你可以自已查阅一下access自带的帮助手册中的详细说明。
如果你想控制科目的显示顺序，可以试一下这个语法的作用。PIVOT pivotfield [IN (value1[, value2[, ...]])]

以上是交叉表查询的常见用法。美中不足，这个由向导生成的查询虽然有了行合计，但没有列合计。由于TRANSFORM 自身功能的限制无法直接生成列合计运算(我们这里所说的合计运算包括平均/最大/最小等，以下均不再说明)。但我们可以通过UNION联合来实现。

思路：直接在table3的数据中追加上合计行然后再进行交叉。

比如如果table3的数据能形成如下记录
+----+---------+-------+--------+------+-------------+
|id  |sName |sClass |Course  |Score |homeworkScore|
+----+---------+-------+--------+------+-------------+
|1 |AAA    |3    |语文 |50 |76          |
|2 |AAA    |3    |数学 |83 |77          |
|3 |AAA    |3    |英语 |65 |60          |
...
|31  |LL    |5    |语文 |80 |75          |
|32  |LL    |5    |数学 |95 |70          |
| |Average  |    |英语 |86 |          |
| |Average  |    |数学 |77 |          |
| |Average  |    |英语 |99 |          |
+----+---------+-------+--------+------+-------------+

这样我们就可以利用 TRANSFORM 来实现了。

1. 生成合计，你可以通过向导或自己生成这个合计的查询
select course,avg(score)
from table3
group by course

+-------+-----------------+
|course |Expr1001       |
+-------+-----------------+
|数学 |81.3636363636364 |
|英语 |65.4          |
|语文 |77.0909090909091 |
+-------+-----------------+

2. 利用UNION生成交叉表查询的数据源。(这里我们用了UNION ALL,关于UNION的语法说明请自行查阅帮助，同样我们利用 'Total' as sName,null as sClass 生成了两个常数列以保证UNION的两个集合的列数相匹配。)

select sName,sClass,Course,Score
from Table3
union all
select 'Total' as sName,null as sClass,course,avg(score)
from table3
group by course

+-------+--------+-------+-----+
|sName  |sClass  |Course |Score|
+-------+--------+-------+-----+
|AAA |3    |数学 |83 |
|AAA |3    |英语 |65 |
.....
|LL    |5    |数学 |95 |
|Total  |       |数学 |81.36|
|Total  |       |英语 |65.4 |
|Total  |       |语文 |77.09|
+-------+--------+-------+-----+

3. 把这个查询代入到一开的那个交叉查询中，替代原来的table3.
把把所有的table3. 换成 t. 如下

TRANSFORM Sum(t.Score) AS ScoreOfSum
SELECT t.sName, t.sClass, Sum(t.Score) AS [Total Of Score]
FROM Table3
GROUP BY t.sName, t.sClass
PIVOT t.Course;

然后再把 from table3 变成

TRANSFORM Sum(t.Score) AS ScoreOfSum
SELECT t.sName, t.sClass, Sum(t.Score) AS [Total Of Score]
FROM (select sName,sClass,Course,Score
from Table3
union all
select 'Total' as sName,null as sClass,course,avg(score)
from table3
group by course) t
GROUP BY t.sName, t.sClass
PIVOT t.Course;

结果如下
+--------+------+------+-----+-----+-----+
|sName |sClass|Total |数学 |英语 |语文 |
+--------+------+------+-----+-----+-----+
|AAA    |3    |198 |83 |65 |50 |
|BBB    |3    |239 |95 |58 |86 |
.......
|JJJJ |5    |220 |97 |61 |62 |
|LL    |5    |175 |95 |    |80 |
|Total |    |223.85|81.36|65.4 |77.09|
+--------+------+------+-----+-----+-----+

如果我们想再加上每个班的小计
那么就再union上每个班的合计平均值

select 'subtotal' as sName,sClass,course,avg(score)
from table3
group by course,sClass
这样改为
TRANSFORM Sum(t.Score) AS ScoreOfSum
SELECT t.sName, t.sClass, Sum(t.Score) AS [Total Of Score]
FROM (select sName,sClass,Course,Score
from Table3
union all
select 'subtotal' as sName,sClass,course,avg(score)
from table3
group by course,sClass
union all
select 'Total' as sName,null as sClass,course,avg(score)
from table3
group by course
) t
GROUP BY t.sName, t.sClass,(t.sClass='subtotal'),(t.sClass='Total')
order by (t.sClass='Total') desc,(t.sClass='subtotal') desc,sClass
PIVOT t.Course

上面用了 order by (t.sClass='Total') desc,(t.sClass='subtotal') desc,sClass 来控制排序，以把subtotal, total 放在最后。
+----------+--------+---------+------+------+------+
|sName    |sClass  |Total Of |数学  |英语  |语文  |
+----------+--------+---------+------+------+------+
|AAA    |3    |198    |83 |65 |50 |
|BBB    |3    |239    |95 |58 |86 |
....
|subtotal  |3    |222.4 |81 |67.2  |74.2  |
......
|LL       |5    |175    |95 |    |80 |
|subtotal  |5    |228    |96 |61 |71 |
|Total    |       |223.8545 |81.363|65.4  |77.090|
+----------+--------+---------+------+------+------+

结束语：
显然通过灵活的SQL语句设计我们可以实现多种需要有VBA程序中实现功能。在实际运用中我们需要在各种方案之间来平衡以找到最佳的应用。有时候用程序的效率比较好，有些时候用查询的比较方便，有些时候甚至跳出access用EXCEL可能更容易。

下篇预告：《交叉表查询中的多个项目列的解决》, 如果在交叉表中显示 (名次，成绩)

本文的用例:

以下内容需要回复才能看到