|
楼主 |
发表于 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.月份<=a1.月份 and a.产品=a1.产品 group by 产品) as 数量
- from
- (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
- union all
- select a3.产品,"合计",a3.数量 from
- (
- select 产品,月份,
- (select last(数量) from
- (select month(日期) as 月份,产品,last(数量) as 数量 from [Sheet1$a:c]where 数量 is not null group by month(日期),产品)a
- where a.月份<=a1.月份 and a.产品=a1.产品 group by 产品) as 数量
- from
- (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
- )a3,
- (select max(month(日期)) as 月份 from [Sheet1$a:c]where 日期 is not null)b3
- where a3.月份=b3.月份
复制代码
增加一个用纯SQL完成的.
- TRANSFORM first(数量)
- select 产品 from
- (select 产品,月份,last(数量) as 数量 from
- (select a1.产品,a1.月份,数量 from
- (select a.产品,month(b.日期) as 月份 from [Sheet1$]a,[Sheet1$]b where a.产品 is not null and b.日期 is not null)a1,
- (select month(日期) as 月份,产品,last(数量) as 数量 from [Sheet1$]where 数量 is not null group by month(日期),产品)b1
- where a1.月份>=b1.月份 and a1.产品=b1.产品)
- group by 产品,月份
- union all
- select 产品,"合计",last(数量) as 数量 from
- (select a1.产品,a1.月份,数量 from
- (select a.产品,month(b.日期) as 月份 from [Sheet1$]a,[Sheet1$]b where a.产品 is not null and b.日期 is not null)a1,
- (select month(日期) as 月份,产品,last(数量) as 数量 from [Sheet1$]where 数量 is not null group by month(日期),产品)b1
- where a1.月份>=b1.月份 and a1.产品=b1.产品)
- group by 产品,"合计")
- group by 产品
- PIVOT 月份
复制代码
PS:为方便测试不同情况下是否通用,更改了数据源的数据.
[ 本帖最后由 wuxiang_123 于 2011-3-23 11:42 编辑 ] |
|