如何用EXCEL OLEDB中的SQL语句直接做出数量的按年累计

suwenkai · 发表于 2010-8-11 21:56

参考一下，能做到年累计
select 年份,类型,(select  sum(总计数量) from
(select year(发货日期) as 年份,类型,sum(数量) as 总计数量 from [发货清单$] GROUP BY  类型,year(发货日期)) a1
where a1.类型=a2.类型 and a1.年份<=a2.年份)  from (select year(发货日期) as 年份,类型,sum(数量) as 总计数量 from [发货清单$] GROUP BY  类型,year(发货日期)) a2

[ 本帖最后由 suwenkai 于 2010-8-12 10:18 编辑 ]

抹香鲸2009 · 发表于 2010-8-12 08:05

楼上结果似乎不对吧。比如10单的2005和2010都应该有数值的

[ 本帖最后由抹香鲸2009 于 2010-8-12 08:06 编辑 ]

suwenkai · 发表于 2010-8-12 08:31

有一个缺点，就是如果有一个项目没有数据，就会出错。。。

zyh196712 · 发表于 2010-8-27 12:39

这里有个类似的贴子，可供参考，只是那个空值暂时还不知怎么转为0值。

[求助] 数据透视表怎么实现我要的结果

zyh196712 · 发表于 2010-8-28 21:15

不知怎么转置：
select year(发货日期) as 年份,类型,sum(数量) as [数量] from [发货清单$] group by year(发货日期),类型 union all select 年份&'年累计' as 年份累计,类型,(select sum(数量1) from (select year(发货日期) as 年份,类型,sum(数量) as 数量1 from [发货清单$] GROUP BY 类型,year(发货日期)) a1 where a1.类型=a2.类型 and a1.年份<=a2.年份) from (select distinct year(发货日期) as 年份,类型 from [发货清单$] ) a2

wuxiang_123 · 发表于 2010-8-29 17:13

先作第一部分：实现纵向累计汇总。

SELECT 类型,年份,SUM(数量) as 数量 FROM
(
select 类型,年份&"累计" AS 年份,
(select sum(总计数量) as 数量 from
(select year(发货日期) as 年份,类型,sum(数量) as 总计数量 from [发货清单$] GROUP BY 类型,year(发货日期)) a1 where a1.类型=a2.类型 and a1.年份<=a2.年份) AS 数量
from
(select DISTINCT 类型,年份 from
(select DISTINCT 类型 from [发货清单$])a,
(select DISTINCT year(发货日期) as 年份 from [发货清单$])b)A2
union all
select 类型,YEAR(发货日期),SUM(数量) FROM [发货清单$] GROUP BY 类型,year(发货日期)
UNION ALL
select DISTINCT 类型,年份,NULL from
(select DISTINCT 类型 from [发货清单$])T,
(select DISTINCT year(发货日期) as 年份 from [发货清单$])F
) GROUP BY 类型,年份 ORDER BY 类型,年份

复制代码

[ 本帖最后由 wuxiang_123 于 2010-8-30 13:48 编辑 ]

wuxiang_123 · 发表于 2010-8-30 16:32

总算弄出来了。

transform sum(数量) select 年份 from
(SELECT 类型,年份,SUM(数量) as 数量 FROM
(
select A2.类型,A2.年份&"累计" AS 年份,sum(A1.S) as 数量 from
(select year(发货日期) as 年份,类型,sum(数量) as S from [发货清单$] GROUP BY 类型,year(发货日期)) a1,
(select DISTINCT 类型,年份 from
(select DISTINCT 类型 from [发货清单$])a,
(select DISTINCT year(发货日期) as 年份 from [发货清单$])b)A2
where a1.类型=a2.类型 and a1.年份<=a2.年份 GROUP BY A2.类型,A2.年份&"累计"
union all
select 类型,YEAR(发货日期),SUM(数量) FROM [发货清单$] GROUP BY 类型,year(发货日期)
UNION ALL
select DISTINCT 类型,年份,NULL from
(select DISTINCT 类型 from [发货清单$])T,
(select DISTINCT year(发货日期) as 年份 from [发货清单$])F
) GROUP BY 类型,年份 ORDER BY 类型,年份
)GROUP BY 年份
pivot 类型

复制代码

部分语句多余，可以简化为：
transform sum(数量) select 年份 from
(
select A2.类型,A2.年份&"累计" AS 年份,sum(A1.S) as 数量 from
(select year(发货日期) as 年份,类型,sum(数量) as S from [发货清单$] GROUP BY 类型,year(发货日期)) a1,
(select 类型,年份 from
(select DISTINCT 类型 from [发货清单$])a,
(select DISTINCT year(发货日期) as 年份 from [发货清单$])b)A2
where a1.类型=a2.类型 and a1.年份<=a2.年份 GROUP BY A2.类型,A2.年份&"累计"
union all
select 类型,YEAR(发货日期),SUM(数量) FROM [发货清单$] GROUP BY 类型,year(发货日期)
UNION ALL
select 类型,年份,NULL from
(select DISTINCT 类型 from [发货清单$])T,
(select DISTINCT year(发货日期) as 年份 from [发货清单$])F
)GROUP BY 年份
pivot 类型

复制代码

再减几个字符：
transform sum(数量) select 年份 from
(
select A2.类型,A2.年份&"累计" AS 年份,A1.S as 数量 from
(select year(发货日期) as 年份,类型,sum(数量) as S from [发货清单$] GROUP BY 类型,year(发货日期)) a1,
(select 类型,年份 from
(select DISTINCT 类型 from [发货清单$])a,
(select DISTINCT year(发货日期) as 年份 from [发货清单$])b)A2
where a1.类型=a2.类型 and a1.年份<=a2.年份
union all
select 类型,YEAR(发货日期),数量 FROM [发货清单$]
UNION ALL
select 类型,年份,NULL from
(select DISTINCT 类型 from [发货清单$])T,
(select DISTINCT year(发货日期) as 年份 from [发货清单$])F
)GROUP BY 年份
pivot 类型

复制代码

[ 本帖最后由 wuxiang_123 于 2010-8-30 17:06 编辑 ]

suwenkai · 发表于 2010-8-30 16:37

先留个位置，晚上再上。

whsfhwm · 发表于 2010-8-30 21:05

实在是高！
非常感谢~~~

suwenkai · 发表于 2010-8-30 22:25

思路算是看明白了,解决了我上次说的,年份没有项目的问题

		自动登录	找回密码
密码			免费注册

[求助] 如何用EXCEL OLEDB中的SQL语句直接做出数量的按年累计

回复 11楼 suwenkai 的帖子

回复 17楼 wuxiang_123 的帖子