交叉表查詢中的累計
時間：2009-08-01 08:50 來源：www.accessbbs.cn 作者：ACMAIN.C… 閱讀：次
交叉表查詢無疑有使用中可以很方便的進行數(shù)據(jù)的分析處理。你可以通過向?qū)砩桑ㄔ谙驅(qū)е心憧梢赃x擇是否生成行合計）或者直接按照這個access特有JET-SQL語法來寫這個SQL語句。
TRANSFORM合計函數(shù)
selectstatement
TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]
比如現(xiàn)有表 table3, 數(shù)據(jù)如下
+----+---------+-------+--------+------+-------------+
|id |sName |sClass |Course |Score |homeworkScore|
+----+---------+-------+--------+------+-------------+
|1 |AAA |3 |語文 |50 |76 |
|2 |AAA |3 |數(shù)學 |83 |77 |
|3 |AAA |3 |英語 |65 |60 |
|4 |BBB |3 |語文 |86 |72 |
|5 |BBB |3 |數(shù)學 |95 |57 |
.......
|31 |LL |5 |語文 |80 |75 |
|32 |LL |5 |數(shù)學 |95 |70 |
+----+---------+-------+--------+------+-------------+
可以用向?qū)У玫揭粋�每人的成績表如下
+--------+-------+---------------+-------+-------+-------+
|sName |sClass |Total Of Score |數(shù)學 |英語 |語文 |
+--------+-------+---------------+-------+-------+-------+
|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[, ...]])]
以上是交叉表查詢的常見用法。美中不足，這個由向?qū)傻牟樵冸m然有了行合計，但沒有列合計。由于TRANSFORM 自身功能的限制無法直接生成列合計運算(我們這里所說的合計運算包括平均/最大/最小等，以下均不再說明)。但我們可以通過UNION聯(lián)合來實現(xiàn)。
思路：直接在table3的數(shù)據(jù)中追加上合計行然后再進行交叉。
比如如果table3的數(shù)據(jù)能形成如下記錄
+----+---------+-------+--------+------+-------------+
|id |sName |sClass |Course |Score |homeworkScore|
+----+---------+-------+--------+------+-------------+
|1 |AAA |3 |語文 |50 |76 |
|2 |AAA |3 |數(shù)學 |83 |77 |
|3 |AAA |3 |英語 |65 |60 |
...
|31 |LL |5 |語文 |80 |75 |
|32 |LL |5 |數(shù)學 |95 |70 |
| |Average | |英語 |86 | |
| |Average | |數(shù)學 |77 | |
| |Average | |英語 |99 | |
+----+---------+-------+--------+------+-------------+
這樣我們就可以利用 TRANSFORM 來實現(xiàn)了。
1. 生成合計，你可以通過向?qū)Щ蜃约荷蛇@個合計的查詢
select course,avg(score)
from table3
group by course
+-------+-----------------+
|course |Expr1001 |
+-------+-----------------+
|數(shù)學 |81.3636363636364 |
|英語 |65.4 |
|語文 |77.0909090909091 |
+-------+-----------------+
2. 利用UNION生成交叉表查詢的數(shù)據(jù)源。(這里我們用了UNION ALL,關于UNION的語法說明請自行查閱幫助，同樣我們利用 'Total' as sName,null as sClass 生成了兩個常數(shù)列以保證UNION的兩個集合的列數(shù)相匹配。)
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 |數(shù)學 |83 |
|AAA |3 |英語 |65 |
.....
|LL |5 |數(shù)學 |95 |
|Total | |數(shù)學 |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 |數(shù)學 |英語 |語文 |
+--------+------+------+-----+-----+-----+
|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 |數(shù)學 |英語 |語文 |
+----------+--------+---------+------+------+------+
|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語句設計我們可以實現(xiàn)多種需要有VBA程序中實現(xiàn)功能。在實際運用中我們需要在各種方案之間來平衡以找到最佳的應用。有時候用程序的效率比較好，有些時候用查詢的比較方便，有些時候甚至跳出access用EXCEL可能更容易。
下篇預告： 《交叉表查詢中的多個項目列的解決》, 如果在交叉表中顯示 (名次，成績)
本文的用例:
t.zip (12.85 KB)
TRANSFORM合計函數(shù)
selectstatement
TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]
比如現(xiàn)有表 table3, 數(shù)據(jù)如下
+----+---------+-------+--------+------+-------------+
|id |sName |sClass |Course |Score |homeworkScore|
+----+---------+-------+--------+------+-------------+
|1 |AAA |3 |語文 |50 |76 |
|2 |AAA |3 |數(shù)學 |83 |77 |
|3 |AAA |3 |英語 |65 |60 |
|4 |BBB |3 |語文 |86 |72 |
|5 |BBB |3 |數(shù)學 |95 |57 |
.......
|31 |LL |5 |語文 |80 |75 |
|32 |LL |5 |數(shù)學 |95 |70 |
+----+---------+-------+--------+------+-------------+
可以用向?qū)У玫揭粋�每人的成績表如下
+--------+-------+---------------+-------+-------+-------+
|sName |sClass |Total Of Score |數(shù)學 |英語 |語文 |
+--------+-------+---------------+-------+-------+-------+
|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[, ...]])]
以上是交叉表查詢的常見用法。美中不足，這個由向?qū)傻牟樵冸m然有了行合計，但沒有列合計。由于TRANSFORM 自身功能的限制無法直接生成列合計運算(我們這里所說的合計運算包括平均/最大/最小等，以下均不再說明)。但我們可以通過UNION聯(lián)合來實現(xiàn)。
思路：直接在table3的數(shù)據(jù)中追加上合計行然后再進行交叉。
比如如果table3的數(shù)據(jù)能形成如下記錄
+----+---------+-------+--------+------+-------------+
|id |sName |sClass |Course |Score |homeworkScore|
+----+---------+-------+--------+------+-------------+
|1 |AAA |3 |語文 |50 |76 |
|2 |AAA |3 |數(shù)學 |83 |77 |
|3 |AAA |3 |英語 |65 |60 |
...
|31 |LL |5 |語文 |80 |75 |
|32 |LL |5 |數(shù)學 |95 |70 |
| |Average | |英語 |86 | |
| |Average | |數(shù)學 |77 | |
| |Average | |英語 |99 | |
+----+---------+-------+--------+------+-------------+
這樣我們就可以利用 TRANSFORM 來實現(xiàn)了。
1. 生成合計，你可以通過向?qū)Щ蜃约荷蛇@個合計的查詢
select course,avg(score)
from table3
group by course
+-------+-----------------+
|course |Expr1001 |
+-------+-----------------+
|數(shù)學 |81.3636363636364 |
|英語 |65.4 |
|語文 |77.0909090909091 |
+-------+-----------------+
2. 利用UNION生成交叉表查詢的數(shù)據(jù)源。(這里我們用了UNION ALL,關于UNION的語法說明請自行查閱幫助，同樣我們利用 'Total' as sName,null as sClass 生成了兩個常數(shù)列以保證UNION的兩個集合的列數(shù)相匹配。)
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 |數(shù)學 |83 |
|AAA |3 |英語 |65 |
.....
|LL |5 |數(shù)學 |95 |
|Total | |數(shù)學 |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 |數(shù)學 |英語 |語文 |
+--------+------+------+-----+-----+-----+
|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 |數(shù)學 |英語 |語文 |
+----------+--------+---------+------+------+------+
|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語句設計我們可以實現(xiàn)多種需要有VBA程序中實現(xiàn)功能。在實際運用中我們需要在各種方案之間來平衡以找到最佳的應用。有時候用程序的效率比較好，有些時候用查詢的比較方便，有些時候甚至跳出access用EXCEL可能更容易。
下篇預告： 《交叉表查詢中的多個項目列的解決》, 如果在交叉表中顯示 (名次，成績)
本文的用例:
以下內(nèi)容需要回復才能看到
t.zip (12.85 KB)
(責任編輯：admin)
頂一下
(1)
100%
踩一下
(0)
0%
相關內(nèi)容
- ·sql語句中l(wèi)ike通配符的匯總（*?!#-等含
- ·Access常用sql基本查詢語句匯總
- ·SQL查詢語句的一般格式小結
- ·聯(lián)合查詢應用舉例1
- ·如何取消操作查詢的提示
- ·報表中先按組再按條件統(tǒng)計
- ·測試查詢速度
- ·條件選擇列求和
- ·SQL必知必會（14）NOT操作符
- ·ACCESS的參數(shù)化查詢-Access數(shù)據(jù)庫教程
- ·ACCESS的真假：四、＂SELECT * INTO 工
- ·在Access中利用搜索窗體中的值生成動態(tài)
- ·Access使用查詢
- ·隨機得到Access數(shù)據(jù)庫記錄
- ·SQL 高級使用
- ·交叉表查詢中的累計
最新內(nèi)容
推薦內(nèi)容