交叉表查询
交叉表查询中的累计
2009-08-01 08:50:24
aggfunctionselectstatementPIVOT 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 ScoreOfSumSELECT Table3.sName, Table3.sClass, Sum(Table3.Score) AS [Total Of Score]FROM Table3GROUP BY Table3.sName, Table3.sClassPIVOT 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 table3group 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,Scorefrom Table3union allselect 'Total' as sName,null as sClass,course,avg(score) from table3group 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 ScoreOfSumSELECT t.sName, t.sClass, Sum(t.Score) AS [Total Of Score]FROM Table3GROUP BY t.sName, t.sClassPIVOT t.Course; 然后再把 from table3 变成TRANSFORM Sum(t.Score) AS ScoreOfSumSELECT 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) tGROUP BY t.sName, t.sClassPIVOT 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 table3group by course,sClass 这样改为TRANSFORM Sum(t.Score) AS ScoreOfSumSELECT 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) tGROUP BY t.sName, t.sClass,(t.sClass='subtotal'),(t.sClass='Total')order by (t.sClass='Total') desc,(t.sClass='subtotal') desc,sClassPIVOT 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可能更容易。下篇预告： 《交叉表查询中的多个项目列的解决》, 如果在交叉表中显示 (名次，成绩)本文的用例:
(报表设计-相关文章技巧链接)：Access如何对交叉查询产生报表
以下内容需要回复才能看到
t.zip (12.85 KB)