交叉表查询中的多列显示

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

Mysql5 实现交叉表查询

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

水晶报表中几种交叉表的实现方法

水晶报表中几种交叉表的实现方法 交叉表,顾名思义,就是行和列交叉,用于将行上的数据,作为列指标来呈现。 用一个图直观的说明一下,就是将图1中的数据样式 最终呈现为如图2的效果 实际在使用中...
  • postfxj
  • postfxj
  • 2016年10月05日 09:39
  • 1077

SQL动态生成交叉表应用范例

交叉表的应用较为丰富,应用的方式与范围也是十分丰富。
  • qiushuisen
  • qiushuisen
  • 2014年10月17日 07:26
  • 1121

Mysql使用多列索引查询

在mysql中,如果使用多列索引进行查询,那么命中的记录是按插入的顺序还是按key的顺序返回呢? 这个问题在不同的存储引擎下,结果是不一样的,这里仅介绍InnoDB下的情形(按key的顺序返回)。 在...
  • sky_ground
  • sky_ground
  • 2017年02月04日 19:14
  • 620

SQL的增删改查(详解)

1增 1.1【插入单行】 insert [into] (列名) values (列值) 例:insert into Strdents (姓名,性别,出生日期) values ('开心朋朋'...
  • qq1435382499
  • qq1435382499
  • 2016年07月11日 08:48
  • 111

oracle 实现分组结果集整合到一行多列显示

刚刚接触数据库的真正应用,刚开始有很多困难,在网上找答案也没有十分靠谱的结果,现在我把自己的经验总结下来,希望能给广大小鲜肉一点灵感,不胜荣幸。 比如要实现如下的功能,将表中的数据检索出来,放入到下...
  • Run_youngman
  • Run_youngman
  • 2016年12月22日 15:55
  • 1341

13.子查询返回多行多列的数据

假设有下面两张表: 部门表dept 雇员表emp         列出公司各个部门的经理的姓名、薪金、部门名称、部门人数、部门平均工资。 步骤1:查找每个部门经理的姓名和薪金。 selec...
  • lzhui1987
  • lzhui1987
  • 2017年02月17日 11:27
  • 1412

sql列出一个表所有列的列名

参考文章:http://zhidao.baidu.com/question/259355257.html ----------------------------------------------...
  • friendan
  • friendan
  • 2013年05月22日 23:35
  • 8611

PostgreSQL 实现交叉表(行列转换)的五种方法

这里我来演示下在POSTGRESQL里面如何实现交叉表的展示,至于什么是交叉表,我就不多说了,度娘去哦。 原始表数据如下: [sql] view plaincopy ...
  • a258831020
  • a258831020
  • 2015年09月14日 21:00
  • 3129

excel中两列如何进行交叉排列组合

除非注明,文章均为网站运营博客原创,转载请注明本文地址:http://blog.52yuding.com/question/473.html...
  • Duke147
  • Duke147
  • 2014年05月20日 15:40
  • 4201
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:交叉表查询中的多列显示
举报原因:
原因补充:

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