Access编程交流网
  • 设为首页|收藏本站|繁体中文|手机版
  •     
  • Access培训-Access开发平台-Access行业开发

  • 首页
  • 资讯
  • 技巧
  • 源码
  • 行业
  • 资源
  • 活动
  • 关于

技巧

ACCESS数据库

启动/设置/选项/背景

修复/压缩

安全/加密/解密

快捷键

版本升级/其它等

数据表

命名方式/设计规范

表设计

查询

Sql语言基础

选择查询

更新查询

删除查询

追加查询

生成表查询

交叉表查询

SQL特定查询

查询参数

查询综合应用

界面/窗体/控件

标签

文本框

命令按钮

组合框/列表框

选项组/复选框/选项按钮

选项卡

子窗体

窗体本身/综合应用

其它

报表打印

报表设计

高级报表

模块/函数/VBA/API/系统

VBA基础

内置函数

调试/跟踪/Debug

模块/类模块

API/COM/系统相关

字符数字日期

网络通信游戏

加密解密安全

文件处理

经典算法

宏/菜单/工具栏/功能区

宏/脚本

菜单/工具栏

功能区/Ribbon

图表/图形/图像/多媒体

图表

图形/图像

音频

视频/动画

DAO/链接表/ADO/ADP

DAO/链接表/ODBC

ADO/RDO

ADP

ActiveX/第三方控件/插件

Treeview树控件

ListView列表控件

Toolbar工具栏控件

微软其它控件

Dbi-Tech

CodeJock

Grid++Report

FastReport

ComponentOne

加载项/插件/Addin

OFFICE集成/导入导出/交互

Excel导入导出/交互

Word导入导出/交互

PPT交互

Outlook控制/邮件

Text文本文件/INI/CSV

PDF/SWF/XML格式

CAD格式

Sharepoint/其它Office

SqlServer/其它数据库

表

视图

存储过程/触发器

函数

用户/权限/安全

调试/维护

SqlServer其它/综合

发布/打包/文档/帮助

开发版/运行时

打包/发布/部署

开发文档/帮助制作

Access完整行业系统

采购管理系统

销售管理系统

仓库管理系统

人力资源管理HRM

CRM管理系统

MRP/ERP管理系统

BRP/流程优化

其它管理系统

心得/经验/绝招
其它/杂项
Excel技巧

Excel应用与操作

Excel开发编程

Word技巧

Word应用与操作

Word开发编程

Outlook技巧

Outlook应用与操作

Outlook开发编程

热门文章

  • ADP(SQL SERVE..
  • 交叉表查询中的累计

最新文章

  • 交叉表查询中的累计
  • ADP(SQL SERVE..

联系方式

Access交流网(免费Access交流)

QQ:18449932 

网  址:www.access-cn.com

当前位置:首页 > 技巧 > 查询 > 交叉表查询
交叉表查询

交叉表查询中的累计

交叉表查询无疑有使用中可以很方便的进行数据的分析处理。你可以通过向导来生成(在向导中你可以选择是否生成行合计)或者直接按照这个access特有JET-SQL语法来写这个SQL语句。

TRANSFORM合计函数
    selectstatement
    TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]


比如现有表 table3, 数据如下
+----+---------+-------+--------+------+-------------+
|id  |sName    |sClass |Course  |Score |homeworkScore|
+----+---------+-------+--------+------+-------------+
|1   |AAA      |3      |语文    |50    |76           |
|2   |AAA      |3      |数学    |83    |77           |
|3   |AAA      |3      |英语    |65    |60           |
|4   |BBB      |3      |语文    |86    |72           |
|5   |BBB      |3      |数学    |95    |57           |
.......

|31  |LL       |5      |语文    |80    |75           |
|32  |LL       |5      |数学    |95    |70           |
+----+---------+-------+--------+------+-------------+
可以用向导得到一个每人的成绩表如下
+--------+-------+---------------+-------+-------+-------+
|sName   |sClass |Total Of Score |数学   |英语   |语文   |
+--------+-------+---------------+-------+-------+-------+
|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[, ...]])]

以上是交叉表查询的常见用法。美中不足,这个由向导生成的查询虽然有了行合计,但没有列合计。由于TRANSFORM 自身功能的限制无法直接生成列合计运算(我们这里所说的合计运算包括平均/最大/最小等,以下均不再说明)。但我们可以通过UNION联合来实现。

思路:直接在table3的数据中追加上合计行然后再进行交叉。

比如如果table3的数据能形成如下记录
+----+---------+-------+--------+------+-------------+
|id  |sName    |sClass |Course  |Score |homeworkScore|
+----+---------+-------+--------+------+-------------+
|1   |AAA      |3      |语文    |50    |76           |
|2   |AAA      |3      |数学    |83    |77           |
|3   |AAA      |3      |英语    |65    |60           |
...
|31  |LL       |5      |语文    |80    |75           |
|32  |LL       |5      |数学    |95    |70           |
|    |Average  |       |英语    |86    |            |
|    |Average  |       |数学    |77    |            |
|    |Average  |       |英语    |99    |            |
+----+---------+-------+--------+------+-------------+


这样我们就可以利用 TRANSFORM 来实现了。

1. 生成合计,你可以通过向导或自己生成这个合计的查询
select course,avg(score)
from table3
group by course


+-------+-----------------+
|course |Expr1001         |  
+-------+-----------------+
|数学   |81.3636363636364 |
|英语   |65.4             |
|语文   |77.0909090909091 |
+-------+-----------------+

2. 利用UNION生成交叉表查询的数据源。(这里我们用了UNION ALL,关于UNION的语法说明请自行查阅帮助,同样我们利用 'Total' as sName,null as sClass 生成了两个常数列以保证UNION的两个集合的列数相匹配。)

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       |数学   |83   |
|AAA    |3       |英语   |65   |
.....
|LL     |5       |数学   |95   |
|Total  |        |数学   |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 |数学 |英语 |语文 |
+--------+------+------+-----+-----+-----+
|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 |数学  |英语  |语文  |
+----------+--------+---------+------+------+------+
|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语句设计我们可以实现多种需要有VBA程序中实现功能。在实际运用中我们需要在各种方案之间来平衡以找到最佳的应用。有时候用程序的效率比较好,有些时候用查询的比较方便,有些时候甚至跳出access用EXCEL可能更容易。

下篇预告: 《交叉表查询中的多个项目列的解决》, 如果在交叉表中显示 (名次,成绩)

本文的用例:
以下内容需要回复才能看到

access数据库 t.zip (12.85 KB)
access数据库 t.zip (12.85 KB)
下载次数: 58
2009-4-26 12:20



(报表设计-相关文章技巧链接):
Access如何对交叉查询产生报表
发布人:ACMAIN.C…-www.accessbbs.cn  
分享到:
点击次数:  更新时间:2009-08-01 08:50:24  【打印此页】  【关闭】
上一条:Dlookup在查询中满足2个条件的用法示例  下一条:实现自动编码功能!



相关文章

  • • ADP(SQL SERVER)中的交叉查询

热门文章

  • [2009-08-01] 交叉表查询中的累计access数据库
  • [2004-11-23] ADP(SQL SERVER)中的交叉查询access数据库

热门产品

公司动态|在线留言|在线反馈|友情链接|会员中心|站内搜索|网站地图

中山市天鸣科技发展有限公司 版权所有 1999-2023 粤ICP备10043721号

QQ:18449932

Access应用 Access培训 Access开发 Access平台

access|数据库|access下载|access教程|access视频|access软件

Powered by MetInfo 5.3.12 ©2008-2025  www.metinfo.cn