交叉資料表查詢中的累計

交叉資料表查詢無疑有使用中可以很方便的進行資料的分析處理。你可以通過嚮導來產生（在嚮導中你可以選擇是否產生行合計）或者直接按照這個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可能更容易。