交叉表查询中的多列显示

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

在Excel的交叉表中定位数据(亲自实践)

有时候我们需要在二维交叉表(或者数据透视表结果)中定位所需要的数据, 如下图: 我们需要输入型号和速度,然后自动得到两者交叉的数据,如下图:   公式如下: =VLOOKUP(F3,...
  • bigheadsheep
  • bigheadsheep
  • 2012-11-16 09:58:31
  • 1867

交叉表、行列转换和交叉查询经典

交叉表、行列转换和交叉查询经典 一、什么是交叉表 “交叉表”对象是一个网格,用来根据...
  • cngkqy
  • cngkqy
  • 2007-12-05 16:01:00
  • 17187

C#行列转换(交叉表)处理源代码

///         /// 将DataTable的第二列的值转化为列(即将原来的行表,转化成交叉表,没有对应值则默认0)贾世义         ///         /// 必须三列,...
  • jsyhello
  • jsyhello
  • 2011-10-20 08:11:02
  • 626

如何生成交叉表?

http://blog.csdn.net/demongz/archive/2005/03/22/327169.aspx在关系数据库中,经常为了使数据库更符合第三范式,同时也是从数据存取速度和空间使用率...
  • foreveryday007
  • foreveryday007
  • 2005-03-23 10:20:00
  • 1036

如何交叉取一个表的数据

有这样一个需求,先说下表结构,这个表里有帖子信息和问答信息,1对1或者1对多。type 1是帖子信息类型 2 是问答类型 CREATE TABLE `wenda` (   `id` int(11)...
  • Truong
  • Truong
  • 2017-06-07 12:47:41
  • 202

交叉表查询中的多列显示。

利用交叉表,我们可以对某个列字段的项目进行显示,比如[学科]来显示[考试成绩],那么是否能同时显示其[作业成绩]在同一个交叉表查询中?比如下面数据Table3+-----+--------+-----...
  • ACMAIN_CHM
  • ACMAIN_CHM
  • 2009-04-26 15:23:00
  • 2682

Mysql5 实现交叉表查询

交叉表、行列转换和交叉查询经典 一、什么是交叉表 “交叉表”对象是一个网格,用来根据指定的条件返回值。数据显示在压缩行和列中。这种格式易于比较数据并辨别其趋势。它由三个元素组成:     行...
  • a437629292
  • a437629292
  • 2015-12-15 17:04:17
  • 2208

RS交叉表自动汇总后百分比列显示错误之解决方案

可以说在从事Cognos开发的过程中,仅仅对数据展现而言,大多数用户使用最多的工具便是Report Studio了,此工具可以帮助我们快速的构建一些可供用户自主选择的数据报告。当然我个人认为没有什么开...
  • goodmoringking
  • goodmoringking
  • 2017-07-12 17:26:24
  • 231

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

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

SqlServer生成交叉表大全

 SqlServer如何生成动态交叉表查询VB+MS SqlServer,是我们目前开发数据库应用系统最常用的模式,翻翻以前的老帖子,有一些SqlServer的问题经常被提出来,但正确解答甚少,现把我...
  • cngkqy
  • cngkqy
  • 2007-12-05 16:39:00
  • 12742
收藏助手
不良信息举报
您举报文章:交叉表查询中的多列显示
举报原因:
原因补充:

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