直接按周 透视 数据

kumoo

这个是我已写好的，用excel透视acess里面的数据，因为数据量很大，有325M且以后还要增加，所以我想直接按周来透视汇总数据，这样提高透视的速度。（不是透视完数据，然后按周计算。）

1. select 经销商名称,款号,综合类别,商品颜色,性别,商务大类,商务小类,日期,年份,实际发货数量,0 as 实际销售数量,0 as 实际零售数量,实际发货金额,0 as 实际销售金额,0 as 实际零售金额,预计发货成本额,0 as 预计销售成本额,0 as 零售成本额,预计发货毛利,0 as 预计销售毛利,0 as 预计零售毛利,供应商名称,采购单价,材质,0 as 订单数量,跟型,筒高 from [发货查询] union all
   
2. 
   
3. select 经销商名称,款号,综合类别,商品颜色,性别,商务大类,商务小类,日期,年份,0 as 实际发货数量,实际销售数量,0 as 实际零售数量,0 as 实际发货金额,实际销售金额,0 as 实际零售金额,0 as 预计发货成本额,预计销售成本额,0 as 预计零售成本额,0 as 预计发货毛利,预计销售毛利,0 as 预计零售毛利,供应商名称,采购单价,材质,0 as 订单数量,跟型,筒高 from [销售查询] union all
   
4. 
   
5. select 经销商名称,款号,综合类别,商品颜色,性别,商务大类,商务小类,日期,年份,0 as 实际发货数量,0 as 实际销售数量,实际零售数量,0 as 实际发货金额,0 as 实际销售金额,实际零售金额,0 as 预计发货成本额,0 as 预计销售成本额,预计零售成本额,0 as 预计发货毛利,0 as 预计销售毛利,预计零售毛利,供应商名称,采购单价,材质,0 as 订单数量,跟型,筒高 from [零售查询] UNION ALL
   
6. 
   
7. select 经销商名称,款号,综合类别,商品颜色,性别,商务大类,商务小类,日期,年份,0 as 实际发货数量,0 as 实际销售数量,0 as 实际零售数量,0 as 实际发货金额,0 as 实际销售金额,0 as 实际零售金额,0 as 预计发货成本额,0 as 预计销售成本额,0 as 预计零售成本额,0 as 预计发货毛利,0 as 预计销售毛利,0 as 预计零售毛利,供应商名称,采购单价,材质,订单数量,跟型,筒高 from [订单查询];

复制代码

是否可以这样：

1. select 经销商名称,款号,综合类别,商品颜色,性别,商务大类,商务小类,year(日期) &"年第" & datediff('ww',year(日期)&'-1-1',日期)+1 & "周" as 按周,年份,实际发货数量,0 as 实际销售数量,0 as 实际零售数量,实际发货金额,0 as 实际销售金额,0 as 实际零售金额,预计发货成本额,0 as 预计销售成本额,0 as 零售成本额,预计发货毛利,0 as 预计销售毛利,0 as 预计零售毛利,供应商名称,采购单价,材质,0 as 订单数量,跟型,筒高 from [发货查询] union all
   
2. 
   
3. select 经销商名称,款号,综合类别,商品颜色,性别,商务大类,商务小类,year(日期) &"年第" & datediff('ww',year(日期)&'-1-1',日期)+1 & "周" as 按周,年份,0 as 实际发货数量,实际销售数量,0 as 实际零售数量,0 as 实际发货金额,实际销售金额,0 as 实际零售金额,0 as 预计发货成本额,预计销售成本额,0 as 预计零售成本额,0 as 预计发货毛利,预计销售毛利,0 as 预计零售毛利,供应商名称,采购单价,材质,0 as 订单数量,跟型,筒高 from [销售查询] union all
   
4. 
   
5. select 经销商名称,款号,综合类别,商品颜色,性别,商务大类,商务小类,year(日期) &"年第" & datediff('ww',year(日期)&'-1-1',日期)+1 & "周" as 按周,年份,0 as 实际发货数量,0 as 实际销售数量,实际零售数量,0 as 实际发货金额,0 as 实际销售金额,实际零售金额,0 as 预计发货成本额,0 as 预计销售成本额,预计零售成本额,0 as 预计发货毛利,0 as 预计销售毛利,预计零售毛利,供应商名称,采购单价,材质,0 as 订单数量,跟型,筒高 from [零售查询] UNION ALL
   
6. 
   
7. select 经销商名称,款号,综合类别,商品颜色,性别,商务大类,商务小类,year(日期) &"年第" & datediff('ww',year(日期)&'-1-1',日期)+1 & "周" as 按周,年份,0 as 实际发货数量,0 as 实际销售数量,0 as 实际零售数量,0 as 实际发货金额,0 as 实际销售金额,0 as 实际零售金额,0 as 预计发货成本额,0 as 预计销售成本额,0 as 预计零售成本额,0 as 预计发货毛利,0 as 预计销售毛利,0 as 预计零售毛利,供应商名称,采购单价,材质,订单数量,跟型,筒高 from [订单查询];

复制代码

kumoo

我感觉第二种方法本质上也是按日期来透视的，请问高手，有没有更好或者更快速有效的方法？

[ 本帖最后由 kumoo 于 2010-4-29 09:53 编辑 ]

eleven_nono

不是可以用分组功能按周显示么?为什么要这样写?

kumoo

给点指导吧

kumoo

select 经销商名称,款号,综合类别,商品颜色,性别,商务大类,商务小类,DatePart("ww",[日期]) AS 週,年份,0 as 实际发货数量,0 as 实际销售数量,0 as 实际零售数量,0 as 实际发货金额,0 as 实际销售金额,0 as 实际零售金额,0 as 预计发货成本额,0 as 预计销售成本额,0 as 预计零售成本额,0 as 预计发货毛利,0 as 预计销售毛利,0 as 预计零售毛利,供应商名称,采购单价,材质,订单数量,跟型,筒高 from [订单查询] GROUP BY DatePart("ww",[日期]) where （按经销商名称和款号汇总）

kumoo

直接在ACESS查询里，修改日期为：（周: Year([日期]) & "年第" & DateDiff('ww',Year([日期]) & '-1-1',[日期])+1 & "周"）

我想会有改善

平帆

你不发附件，别人很难参与

kumoo

SELECT 总部发货表.经销商名称, 总部发货表.款号, Year([日期]) & "年第" & DateDiff('ww',Year([日期]) & '-1-1',[日期])+1 & "周" AS 周, Sum(总部发货表.实际发货数量) AS 实际发货数量之总计, Sum(总部发货表.实际发货金额) AS 实际发货金额之总计, Sum(总部发货表.预计发货成本额) AS 预计发货成本额之总计, Sum(总部发货表.预计发货毛利) AS 预计发货毛利之总计, 款号信息档案查询.年份, 款号信息档案查询.性别, 款号信息档案查询.综合类别, 款号信息档案查询.商务大类, 款号信息档案查询.商务小类, 款号信息档案查询.商品颜色, 款号信息档案查询.采购单价, 款号信息档案查询.材质, 款号信息档案查询.供应商名称, 款号信息档案查询.供应商编号, 款号信息档案查询.跟型, 款号信息档案查询.筒高
FROM 款号信息档案查询 RIGHT JOIN 总部发货表 ON 款号信息档案查询.款号 = 总部发货表.款号
GROUP BY 总部发货表.经销商名称, 总部发货表.款号, Year([日期]) & "年第" & DateDiff('ww',Year([日期]) & '-1-1',[日期])+1 & "周", 款号信息档案查询.年份, 款号信息档案查询.性别, 款号信息档案查询.综合类别, 款号信息档案查询.商务大类, 款号信息档案查询.商务小类, 款号信息档案查询.商品颜色, 款号信息档案查询.采购单价, 款号信息档案查询.材质, 款号信息档案查询.供应商名称, 款号信息档案查询.供应商编号, 款号信息档案查询.跟型, 款号信息档案查询.筒高;