利用SQL增加累计字段

anthonywang9

目前有一张分年、月统计计划及实际值的数据表，可以据此作出如下的分月统计数据透视表

但为了便于跟踪累计统计数据（截至每月最后一天），需增加三个字段“累计计划移交数”、“累计实际移交数”及“累计移交率”。我在附件excel表中已利用公式模拟计算出这三个字段的累计数据（其中日期列为辅助列），然后丰富数据后可以据此作出数据透视表形式如下（增加了几条累计折线）

请问各位大侠如何利用SQL语句设置数据透视表连接属性，以添加上述三个字段的功能。
我能想到的是利用SQL alter add操作语句似乎只能添加字段，如何设置字段的各记录值向各位高手求教。
增加累计曲线统计数值.rar (10.15 KB, 下载次数: 45)

2012-4-11 21:23 上传

点击文件名下载附件

zhenghui13

累计移交率怎么放到图里面呢？

anthonywang9

谢谢zhenghui13 大侠，不过可能没理解我的意思。
1、excel表中累计计划数、累计实际数、移交率字段各记录值是我通过excel公式计算出来的，是模拟的结果为了方便大家的理解。我所需要的是从 {年/月/计划移交/实际移交} 的源表中利用sql语句（即透视图连接属性）扩充为{年/月/计划移交/实际移交/累计计划数/累计实际数/移交率} 的表格。
2、累计移交率不需要放到透视图中，但需要在透视表中有数据。
再次求教。

anthonywang9

我错了，我没有看到sheet2表格中连接属性已设置为：
SELECT 年,月,计划移交,实际移交,DSUM("计划移交","[SHEET1$]","年&月<='"&年&月&"'") as 累计计划移交,DSUM("实际移交","[SHEET1$]","年&月<='"&年&月&"'") as 累计实际移交,累计实际移交/累计计划移交 as 移交率
from [sheet1$a:d]
谢谢zhenghui13 大侠，问题已解决，误解之处见谅。
不过这个SQL语句怎么理解，大侠能解释解释吗？

anthonywang9

zhenghui13 大侠，其实我利用的access平台，我已经将你的语句简化了字段数，ACCESS平台仍显示 查询过于复杂。。

zhenghui13

ACCESS我不是很熟,不过EXCEL的SQL语名是基于ACCESS的.所以我想你可以看看ACCESS的帮助.

anthonywang9

我在想是不是access的计算能力不如excel
其实我的源数据（包含{年/月/计划移交/实际移交}的源表）是来于另一个联合查询
SELECT 年,月, sum(计划移交数) AS 计划移交, sum(实际移交数) AS 实际移交
FROM (select format(计划移交日期,"yyyy") as 年,format(计划移交日期,"mm") as 月,count(房间号) AS 计划移交数,0 as 实际移交数
FROM [房间移交详表 的副本]
where not isnull([计划移交日期])
group by format(计划移交日期,"yyyy"),format(计划移交日期,"mm")
UNION ALL select format(实际移交日期,"yyyy") as 年,format(实际移交日期,"mm") as 月, 0 as 计划移交数,count(房间号) AS 实际移交数
FROM [房间移交详表 的副本]
where not isnull([实际移交日期])
group by format(实际移交日期,"yyyy"),format(实际移交日期,"mm"))
GROUP BY 年, 月
不知道是不是嵌套的层次多了

anthonywang9

已利用自连接语句优化累计查询的语句，查询效率尚可，特来结贴，代码如下：
SELECT a.年, a.月, a.计划移交, a.实际移交, sum(b.计划移交) AS 累计计划数, sum(b.实际移交) AS 累计实际数, format(sum(b.实际移交)/sum(b.计划移交),"00.0%") AS 累计移交率
FROM 房间移交按月统计查询 AS a, 房间移交按月统计查询 AS b
WHERE [a.年] & [a.月]>=[b.年] & [b.月]
GROUP BY a.年, a.月, a.计划移交, a.实际移交