楼主
wise |
本文来自ACMAIN_CHM的作品改编.
交叉表查询无疑有使用中可以很方便的进行数据的分析处理。你可以通过向导来生成（在向导中你可以选择是否生成行合计）或者直接按照这个Excel(ACCESS也适用）特有JET-SQL语法来写这个SQL语句。
TRANSFORM合计函数
selectstatement
TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]
比如现有表 table3, 数据如下
可以用向导得到一个每人的成绩表如下
它对应的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自带的帮助手册中的详细说明,Excel里面的SQL也是参考Access的写法。
如果你想控制科目的显示顺序，可以试一下这个语法的作用。PIVOT pivotfield [IN (value1[, value2[, ...]])]
以上是交叉表查询的常见用法。美中不足，这个由向导生成的查询虽然有了行合计，但没有列合计。由于TRANSFORM 自身功能的限制无法直接生成列合计运算(我们这里所说的合计运算包括平均/最大/最小等，以下均不再说明)。但我们可以通过UNION联合来实现。
思路：直接在table3的数据中追加上合计行然后再进行交叉。
比如如果table3的数据能形成如下记录
这样我们就可以利用 TRANSFORM 来实现了。
1. 生成合计，你可以通过向导或自己生成这个合计的查询- select course,avg(score)
- from table3
- group by course
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
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;
结果如下
如果我们想再加上每个班的小计
那么就再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 放在最后。
结束语：
显然通过灵活的SQL语句设计我们可以实现多种需要有VBA程序中实现功能。在实际运用中我们需要在各种方案之间来平衡以找到最佳的应用。有时候用程序的效率比较好，有些时候用查询的比较方便，有些时候用EXCEL可能更容易。 |