交叉表查询中的多列显示

原创 2010年01月10日 22:51:00

利用交叉表,我们可以对某个列字段的项目进行显示,比如[学科]来显示[考试成绩],那么是否能同时显示其[作业成绩]在同一个交叉表查询中?

比如下面数据
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中如何实现多列数据同样在交叉查询表中实现的方法。当然还有很多方法。

相关文章推荐

RS导出Excel交叉表角对应的列占用多列问题

在Cognos报表展示的时候,很多用户为了计算会把数据报表导出成excel然后再做统计,于是乎我做的一张报表导出成Excel的时候就出现了这样的问题 从上图可以看出交叉表角对应的列 ‘一级手术’...

显示一对多列表

正向的一对多关系,一般只应用在下拉列表中,通过关联来动态生成多级的下拉列表。  后台完成查询全部省份的方法,在页面上加入超连接。          在Action中完成查询和传递数据的操作。  p...

OCP-1Z0-051 第166题 使用子查询update表中的多列

一、原题 View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables. You want...

C#动态交叉表查询

  • 2013年07月31日 21:27
  • 309KB
  • 下载

不定长字段交叉表查询

  • 2010年07月09日 18:51
  • 1KB
  • 下载

oracle查询排序asc/desc 多列 order by

查询结果的排序 显示EMP表中不同的部门编号。 如果要在查询的同时排序显示结果,可以使用如下的语句: Sql代码 SELECT 字段列表 FROM 表名 WHER...

MySQL种字符串拆分(substring_index)并作为关联查询条件后多列展示

本文欢迎转载,转载请注明出处,谢谢~(作者:喝酒不骑马 Colton_Null) from CSDN最近的项目中有个需求。有关用户的地址信息是存在person_info_t表中的address字...

devexpress LookUpEdit 多列模糊查询

  • 2016年09月07日 10:02
  • 12.88MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:交叉表查询中的多列显示
举报原因:
原因补充:

(最多只允许输入30个字)