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

zyh196712 · 发表于 2010-8-31 00:28

原帖由 wuxiang_123 于 2010-8-30 16:32 发表
总算弄出来了。transform sum(数量) select 年份 from
(select 类型,年份 from
(select DISTINCT 类型 from [发货清单$])a,
(select DISTINCT year(发货日期) as 年份 from [发货清单$])b)A2

高！佩服！
没想到用迪卡儿积！

zyh196712 · 发表于 2010-8-31 00:29

有空再细看。

zyh196712 · 发表于 2010-8-31 20:57

如果要达到楼主的要求，语句还必须修改，必须为以后填写数据预留空单元格，修改的语句如下：
transform sum(iif(isnull(数量),0,数量)) select 年份 from
(select A2.类型,A2.年份&"累计" AS 年份,sum(S) as 数量 from
(select year(发货日期) as 年份,类型,sum(数量) as S from [发货清单$] GROUP BY 类型,year(发货日期)) a1
right join .........这个是关键哦
(select distinct 类型,年份 from (select distinct 类型 from [发货清单$]) a,(select distinct year(发货日期) as 年份 from [发货清单$]) b)A2
on a1.类型=a2.类型 and a1.年份<=a2.年份
GROUP BY A2.类型,A2.年份&"累计"
union all
select 类型,YEAR(发货日期),SUM(数量) FROM [发货清单$] GROUP BY 类型,year(发货日期) )
GROUP BY 年份 pivot 类型

为什么总计会出现不同结果呢？详细见附件，欢迎讨论。

zyh196712 · 发表于 2010-8-31 20:38

前几天，我试过将下面这段进行转置，可怎么弄都不行，今天还是不行，不转置语句没问题，一转置就不行，郁闷啊！

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

我是这样写的：
transform  sum(数量)  select 年份 from
(select year(发货日期) as 年份,类型,sum(数量) as [数量] from [发货清单$] group by  year(发货日期),类型
union all
select  a2.年份&'年累计' as 年份累计,a2.类型,(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 group by a2.年份,a2.类型)
group by 年份 pivot 类型

如果改为如下就可以，真的不知为什么呢！

transform sum(数量汇总) select 年份 from
(select year(发货日期) as 年份,类型,sum(数量) as 数量汇总 from [发货清单$] group by  year(发货日期),类型
union all
select  a2.年份&'年累计' as 年份,a2.类型,sum(数量1) from
(select year(发货日期) as 年份,类型,sum(数量) as 数量1 from [发货清单$] GROUP BY  类型,year(发货日期)) a1,
(select distinct year(发货日期) as 年份,类型 from [发货清单$] ) a2  where a1.类型=a2.类型 and a1.年份<=a2.年份
group by a2.年份,a2.类型)
group by 年份 pivot 类型

zyh196712 · 发表于 2010-8-31 20:22

原帖由 wuxiang_123 于 2010-8-30 16:32 发表
总算弄出来了。再减几个字符：
...

这最后一减值得学习！

在此顺便请教一下，下面这一段在原句中的作用是什么，能解释下吗？我实在看不懂。
select 类型,年份,NULL from
(select DISTINCT 类型 from [发货清单$])T,
(select DISTINCT year(发货日期) as 年份 from [发货清单$])F

我怎么觉得如果按楼主的意思，上一段代码可以不要啊，因此语句还可以再减：
transform sum(数量) select 年份 from
select A2.类型,A2.年份&"累计" AS 年份,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 [发货清单$]
)GROUP BY 年份
pivot 类型

zyh196712 · 发表于 2010-8-31 22:27

transform sum(iif(isnull(数量),0,数量)) select 年份 from
(select A2.类型,A2.年份&"累计" AS 年份,S as [数量] from
(select year(发货日期) as 年份,类型,sum(数量) as S from [发货清单$] GROUP BY 类型,year(发货日期)) a1
right join
(select distinct 类型,年份 from (select distinct 类型 from [发货清单$]) a,
(select distinct year(发货日期) as 年份 from [发货清单$]) b)A2
on a1.类型=a2.类型 and a1.年份<=a2.年份
union all
select 类型,YEAR(发货日期),数量 FROM [发货清单$] )
GROUP BY 年份 pivot 类型

suwenkai · 发表于 2010-8-31 22:45

估计你的数量这个字段有重复了,所以出错了.

zyh196712 · 发表于 2010-9-1 19:31

谢谢你的释疑，我想也是这样，但是这样不能对汇总项起效D

wuxiang_123 · 发表于 2010-9-1 12:35

目的是保留空值，原先用LEFT JOIN连接时，提示“字段太小，请减少复制粘贴的数值”，于是就用UNION ALL来代替

想不到用RIGHT JOIN就可以。学习了。

wuxiang_123 · 发表于 2010-9-1 12:41

我开始也是这样写的，可能transform不支持表嵌套子查询（不是说不能外接子查询哦）。

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

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

回复 17楼 wuxiang_123 的帖子--再次请教

改上一楼的代码

回复 24楼 zyh196712 的帖子

回复 28楼 wuxiang_123 的帖子

回复 23楼 zyh196712 的帖子

回复 24楼 zyh196712 的帖子