利用交叉表，我们可以对某个列字段的项目进行显示，比如[学科]来显示[考试成绩]，那么是否能同时显示其[作业成绩]在同一个交叉表查询中？
比如下面数据
Table3
+—–+——–+——-+——-+——+————-+
|id |sName |sClass |Course |Score |homeworkScore|
+—–+——–+——-+——-+——+————-+
|1 |AAA |3 |语文 |50 |8 |
|2 |AAA |3 |数学 |83 |9 |
|3 |AAA |3 |英语 |65 |7 |
|4 |BBB |3 |语文 |86 |6 |
|5 |BBB |3 |数学 |95 |5 |
…….
|30 |JJJJ |5 |英语 |61 |9 |
|31 |LL |5 |语文 |80 |7 |
|32 |LL |5 |数学 |95 |9 |
+—–+——–+——-+——-+——+————-+
我们可以得到关于科目的交叉表查询 [考试成绩],也可以得到[作业成绩]
TRANSFORM Sum(Table3.Score)
SELECT Table3.sName, Table3.sClass
FROM Table3
GROUP BY Table3.sName, Table3.sClass
PIVOT Table3.Course;
+——-+——+—-+—-+—-+
|sName |sClass|数学|英语|语文|
+——-+——+—-+—-+—-+
|AAA |3 |83 |65 |50 |
|BBB |3 |95 |58 |86 |
|CCC |3 |75 |78 |92 |
|DDD |3 |76 |77 |83 |
…..
|JJJJ |5 |97 |61 |62 |
|LL |5 |95 | |80 |
+——-+——+—-+—-+—-+
TRANSFORM Sum(Table3.homeworkScore)
SELECT Table3.sName, Table3.sClass
FROM Table3
GROUP BY Table3.sName, Table3.sClass
PIVOT Table3.Course;
+——-+——+—-+—-+—-+
|sName |sClass|数学|英语|语文|
+——-+——+—-+—-+—-+
|AAA |3 |8 |6 |8 |
|BBB |3 |6 |7 |7 |
|CCC |3 |7 |9 |9 |
…..
|JJJJ |5 |9 |7 |5 |
|LL |5 |7 | |8 |
+——-+——+—-+—-+—-+
那么如何把这两项合并显示呢？
首先容易想到的方法是，将这两个单独的交叉表查询保存了各自的查询,比如query1,query2,然后再形成基于这两个 query1,query2 的 inner join 查询。
SELECT Query1.*, Query2.*
FROM Query1 INNER JOIN Query2 ON Query1.sName = Query2.sName;
由于 交叉表查询 的列数我们无法预知，所以只能选择 Query1.*, Query2.* 所有的字段。结果如下
+——–+———+——-+——-+——-+——–+———+——-+——-+——-+
|Q1.sName|Q1.sClass|Q1.数学|Q1.英语|Q1.语文|Q2.sName|Q2.sClass|Q2.数学|Q2.英语|Q2.语文|
+——–+———+——-+——-+——-+——–+———+——-+——-+——-+
|AAA |3 |83 |65 |50 |AAA |3 |8 |6 |8 |
|BBB |3 |95 |58 |86 |BBB |3 |6 |7 |7 |
|CCC |3 |75 |78 |92 |CCC |3 |7 |9 |9 |
|DDD |3 |76 |77 |83 |DDD |3 |7 |6 |9 |
|EEE |3 |76 |58 |60 |EEE |3 |5 |8 |5 |
|FFF |4 |62 |60 |85 |FFF |4 |7 |7 |7 |
|GGG |4 |80 |97 |81 |GGG |4 |6 |10 |8 |
|HHHH |4 |88 |50 |74 |HHHH |4 |8 |9 |8 |
|IIIIIII |4 |68 |50 |95 |IIIIIII |4 |5 |7 |8 |
|JJJJ |5 |97 |61 |62 |JJJJ |5 |9 |7 |5 |
|LL |5 |95 | |80 |LL | |7 | |8 |
+——–+———+——-+——-+——-+——–+———+——-+——-+——-+
有没有其它的方法？
如果用户的需求只是为了显示，（交叉表查询好象也只能为了显示，无法更新，想不出还有什么用途）, 则可以通过字符串合并操作把多个字段合并为一个字段。如下 (由于是字符串，这里改sum()为min())
TRANSFORM min(Table3.Score & ' + ' & Table3.homeworkScore)
SELECT Table3.sName, Table3.sClass
FROM Table3
GROUP BY Table3.sName, Table3.sClass
PIVOT Table3.Course;
+——-+——+——-+——-+——-+
|sName |sClass|数学 |英语 |语文 |
+——-+——+——-+——-+——-+
|AAA |3 |83 + 8 |65 + 6 |50 + 8 |
|BBB |3 |95 + 6 |58 + 7 |86 + 7 |
|CCC |3 |75 + 7 |78 + 9 |92 + 9 |
|DDD |3 |76 + 7 |77 + 6 |83 + 9 |
|EEE |3 |76 + 5 |58 + 8 |60 + 5 |
|FFF |4 |62 + 7 |60 + 7 |85 + 7 |
|GGG |4 |80 + 6 |97 + 10|81 + 8 |
|HHHH |4 |88 + 8 |50 + 9 |74 + 8 |
|IIIIIII|4 |68 + 5 |50 + 7 |95 + 8 |
|JJJJ |5 |97 + 9 |61 + 7 |62 + 5 |
|LL |5 |95 + 7 | |80 + 8 |
+——-+——+——-+——-+——-+
如果还要计算，比如导入到EXCEL中，则需要把多列按 交叉表查询 的要求整理一下数据源。
select sName,sClass,Course & ':考试成绩' as sColID,Score as vCol
from Table3
UNION ALL
select sName,sClass,Course & ':作业成绩' as sColID,homeworkScore as vCol
from Table3
+—–+——+————–+—–+
|sName|sClass|sColID |vCol |
+—–+——+————–+—–+
|AAA |3 |数学:考试成绩 |83 |
|AAA |3 |英语:考试成绩 |65 |
|BBB |3 |语文:考试成绩 |86 |
|BBB |3 |数学:考试成绩 |95 |
|AAA |3 |语文:考试成绩 |50 |
|AAA |3 |数学:作业成绩 |8 |
|AAA |3 |英语:作业成绩 |6 |
|BBB |3 |语文:作业成绩 |7 |
|BBB |3 |数学:作业成绩 |6 |
……..
|LL |5 |语文:作业成绩 |8 |
|LL |5 |数学:作业成绩 |7 |
+—–+——+————–+—–+
然后再基于此做交叉查询.
TRANSFORM Sum(t.vCol)
SELECT t.sName, t.sClass
FROM (
select sName,sClass,Course & ':考试成绩' as sColID,Score as vCol
from Table3
UNION ALL
select sName,sClass,Course & ':作业成绩' as sColID,homeworkScore as vCol
from Table3
)t
GROUP BY t.sName, t.sClass
PIVOT t.sColID;
+——-+——+————-+————-+————-+————-+————-+————-+
|sName |sClass|数学:作业成绩|数学:考试成绩|英语:作业成绩|英语:考试成绩|语文:作业成绩|语文:考试成绩|
+——-+——+————-+————-+————-+————-+————-+————-+
|AAA |3 |8 |83 |6 |65 |8 |50 |
|BBB |3 |6 |95 |7 |58 |7 |86 |
|CCC |3 |7 |75 |9 |78 |9 |92 |
|DDD |3 |7 |76 |6 |77 |9 |83 |
|EEE |3 |5 |76 |8 |58 |5 |60 |
|FFF |4 |7 |62 |7 |60 |7 |85 |
|GGG |4 |6 |80 |10 |97 |8 |81 |
|HHHH |4 |8 |88 |9 |50 |8 |74 |
|IIIIIII|4 |5 |68 |7 |50 |8 |95 |
|JJJJ |5 |9 |97 |7 |61 |5 |62 |
|LL |5 |7 |95 | | |8 |80 |
+——-+——+————-+————-+————-+————-+————-+————-+
本文只是探讨了一种在ACCESS中如何实现多列数据同样在交叉查询表中实现的方法。当然还有很多方法。