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

zyh196712

原帖由 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

有空再细看。

zyh196712

如果要达到楼主的要求，语句还必须修改，必须为以后填写数据预留空单元格，修改的语句如下：
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

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

原帖由 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

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

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

zyh196712

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

wuxiang_123

目的是保留空值，原先用LEFT JOIN连接时，提示“字段太小，请减少复制粘贴的数值”，于是就用UNION ALL来代替 想不到用RIGHT JOIN就可以。学习了。

wuxiang_123

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