透视表基础你懂得有几多

fishell

这个要好好学习一下 谢谢楼主精彩讲解！

wxin0708

原帖由 wanghaiqn1957 于 2011-3-21 10:21 发表

我也是，要什么级别才能做到这一点

传说是高级

99253415

香帅出马 一个顶俩 学习中……

wuxiang_123

参考:

1. select 产品,月份,
   
2. (select last(数量) from
   
3. (select month(日期) as 月份,产品,last(数量) as 数量 from [Sheet1$a:c]where 数量 is not null group by month(日期),产品)a
   
4. where a.月份<=a1.月份 and a.产品=a1.产品 group by 产品) as 数量
   
5. from
   
6. (select DISTINCT a.产品,month(b.日期) as 月份 from [Sheet1$a:c]a,[Sheet1$a:c]b where a.产品 is not null and b.日期 is not null)a1
   
7. union all
   
8. select a3.产品,"合计",a3.数量 from
   
9. (
   
10. select 产品,月份,
    
11. (select last(数量) from
    
12. (select month(日期) as 月份,产品,last(数量) as 数量 from [Sheet1$a:c]where 数量 is not null group by month(日期),产品)a
    
13. where a.月份<=a1.月份 and a.产品=a1.产品 group by 产品) as 数量
    
14. from
    
15. (select DISTINCT a.产品,month(b.日期) as 月份 from [Sheet1$a:c]a,[Sheet1$a:c]b where a.产品 is not null and b.日期 is not null)a1
    
16. )a3,
    
17. (select max(month(日期)) as 月份 from [Sheet1$a:c]where 日期 is not null)b3
    
18. where a3.月份=b3.月份

复制代码

增加一个用纯SQL完成的.

1. TRANSFORM first(数量)
   
2. select 产品 from
   
3. (select 产品,月份,last(数量) as 数量 from
   
4. (select a1.产品,a1.月份,数量 from
   
5. (select a.产品,month(b.日期) as 月份 from [Sheet1$]a,[Sheet1$]b where a.产品 is not null and b.日期 is not null)a1,
   
6. (select month(日期) as 月份,产品,last(数量) as 数量 from [Sheet1$]where 数量 is not null group by month(日期),产品)b1
   
7. where a1.月份>=b1.月份 and a1.产品=b1.产品)
   
8. group by 产品,月份
   
9. union all
   
10. select 产品,"合计",last(数量) as 数量 from
    
11. (select a1.产品,a1.月份,数量 from
    
12. (select a.产品,month(b.日期) as 月份 from [Sheet1$]a,[Sheet1$]b where a.产品 is not null and b.日期 is not null)a1,
    
13. (select month(日期) as 月份,产品,last(数量) as 数量 from [Sheet1$]where 数量 is not null group by month(日期),产品)b1
    
14. where a1.月份>=b1.月份 and a1.产品=b1.产品)
    
15. group by 产品,"合计")
    
16. group by 产品
    
17. PIVOT 月份

复制代码

PS:为方便测试不同情况下是否通用,更改了数据源的数据.

[ 本帖最后由 wuxiang_123 于 2011-3-23 11:42 编辑 ]

incognito

原帖由 wuxiang_123 于 2011-3-22 12:22 发表
参考:select 产品,月份,
(select last(数量) from
(select month(日期) as 月份,产品,last(数量) as 数量 from [Sheet1$a:c]where 数量 is not null group by month(日期),产品)a
where a.月份

非常感谢！

lygfj

感谢楼主！学习中！

xmyjk

很不错的教程，受教了。

注水肉

受教了，谢谢楼主！

wanxiangsr

这个要认真学习！

shtong

好东西。顶起来！