两天两夜了，就是找不出原因，到底错在哪来，麻烦大侠们帮帮忙。。

卖猪仔二号

图片一中，我SELECT 了11天的数据，数量的计算项和单价的计算项中就能显示出来，图片二、但是如果到13天或以上就会等于零。。很是奇怪。。金额的计算项又不会。。。真搞不明白了！不就是在隔壁一列而已。。（11日里面有数据，13日里面没数据），但是我又尝试把每日的都加上数据，还是不行。。。。大侠们帮帮忙，小弟万分感谢 每日采购物料清单.rar (130.25 KB, 下载次数: 29)

2012-3-13 11:14 上传

点击文件名下载附件

zm0115

大致来看，有数量列中好像有非数值型。连接代码改成

1. select 品名,sum(数量) as 数量,sum(金额) as 金额 from (select * FROM [1日$B3:G103] union all select * FROM [2日$B3:G103] union all select * FROM [3日$B3:G103] union all select * FROM [4日$B3:G103] union all select * FROM [5日$B3:G103] union all select * FROM [6日$B3:G103] union all select * FROM [7日$B3:G103] union all select * FROM [8日$B3:G103] union all select * FROM [9日$B3:G103] union all select * FROM [10日$B3:G103] union all select * FROM [11日$B3:G103] union all select * FROM [12日$B3:G103] union all select * FROM [13日$B3:G103] union all select * FROM [14日$B3:G103] union all select * FROM [15日$B3:G103] union all select * FROM [16日$B3:G103] union all select * FROM [17日$B3:G103] union all select * FROM [18日$B3:G103] union all select * FROM [19日$B3:G103] union all select * FROM [20日$B3:G103] union all select * FROM [21日$B3:G103] union all select * FROM [22日$B3:G103] union all select * FROM [23日$B3:G103] union all select * FROM [24日$B3:G103] union all select * FROM [25日$B3:G103] union all select * FROM [26日$B3:G103] union all select * FROM [27日$B3:G103] union all select * FROM [28日$B3:G103] union all select * FROM [29日$B3:G103] union all select * FROM [30日$B3:G103] union all select * FROM [31日$B3:G103]) group by 品名

复制代码

好像就可以了

卖猪仔二号

那如果要加上分类呢？group by 品名是什么意思？

卖猪仔二号

我想能按分类、品名、“1日” as 日期，数量加起来，单价平均求平均值。怎么做到你呢？麻烦你啦。


select 分类,品名,数量,单价,"1日" as 日期 FROM [1日$B3:G103] union all select 分类,品名,数量,单价,"2日" as 日期 FROM [2日$B3:G103] union all select 分类,品名,数量,单价,"3日" as 日期 FROM [3日$B3:G103] union  all  select 分类,品名,数量,单价,"4日" as 日期 FROM [4日$B3:G103] union all  select 分类,品名,数量,单价,"5日" as 日期 FROM [5日$B3:G103] union all select 分类,品名,数量,单价,"6日" as 日期 FROM [6日$B3:G103] union all select 分类,品名,数量,单价,"7日" as 日期 FROM [7日$B3:G103] union all select 分类,品名,数量,单价,"8日" as 日期 FROM [8日$B3:G103] union all select 分类,品名,数量,单价,"9日" as 日期 FROM [9日$B3:G103] union all select 分类,品名,数量,单价,"10日" as 日期 FROM [10日$B3:G103] union all select 分类,品名,数量,单价,"11日" as 日期 FROM [11日$B3:G103] union all select 分类,品名,数量,单价,"12日" as 日期 FROM [12日$B3:G103] union all select 分类,品名,数量,单价,"13日" as 日期 FROM [13日$B3:G103] union all select 分类,品名,数量,单价,"14日" as 日期 FROM [14日$B3:G103] union all select 分类,品名,数量,单价,"15日" as 日期 FROM [15日$B3:G103] union all select 分类,品名,数量,单价,"16日" as 日期 FROM [16日$B3:G103] union all select 分类,品名,数量,单价,"17日" as 日期 FROM [17日$B3:G103] union all select 分类,品名,数量,单价,"18日" as 日期 FROM [18日$B3:G103] union all select 分类,品名,数量,单价,"19日" as 日期 FROM [19日$B3:G103] union all select 分类,品名,数量,单价,"20日" as 日期 FROM [20日$B3:G103] union all select 分类,品名,数量,单价,"21日" as 日期 FROM [21日$B3:G103] union all select 分类,品名,数量,单价,"22日" as 日期 FROM [22日$B3:G103] union all select 分类,品名,数量,单价,"23日" as 日期 FROM [23日$B3:G103] union all select 分类,品名,数量,单价,"24日" as 日期 FROM [24日$B3:G103] union all select 分类,品名,数量,单价,"25日" as 日期 FROM [25日$B3:G103] union all select 分类,品名,数量,单价,"26日" as 日期 FROM [26日$B3:G103] union all select 分类,品名,数量,单价,"27日" as 日期 FROM [27日$B3:G103] union all select 分类,品名,数量,单价,"28日" as 日期 FROM [28日$B3:G103] union all select 分类,品名,数量,单价,"29日" as 日期 FROM [29日$B3:G103] union all select 分类,品名,数量,单价,"30日" as 日期 FROM [30日$B3:G103] union all select 分类,品名,数量,单价,"31日" as 日期 FROM [31日$B3:G103]


这是我之前写的

zm0115

卖猪仔二号 发表于 2012-3-13 11:39
那如果要加上分类呢？group by 品名是什么意思？

select 品名,分类,sum(数量) as 数量,avg(单价) as 单价,sum(金额) as 金额 from (select * FROM [1日$B3:G103] union all select * FROM [2日$B3:G103] union all select * FROM [3日$B3:G103] union all select * FROM [4日$B3:G103] union all select * FROM [5日$B3:G103] union all select * FROM [6日$B3:G103] union all select * FROM [7日$B3:G103] union all select * FROM [8日$B3:G103] union all select * FROM [9日$B3:G103] union all select * FROM [10日$B3:G103] union all select * FROM [11日$B3:G103] union all select * FROM [12日$B3:G103] union all select * FROM [13日$B3:G103] union all select * FROM [14日$B3:G103] union all select * FROM [15日$B3:G103] union all select * FROM [16日$B3:G103] union all select * FROM [17日$B3:G103] union all select * FROM [18日$B3:G103] union all select * FROM [19日$B3:G103] union all select * FROM [20日$B3:G103] union all select * FROM [21日$B3:G103] union all select * FROM [22日$B3:G103] union all select * FROM [23日$B3:G103] union all select * FROM [24日$B3:G103] union all select * FROM [25日$B3:G103] union all select * FROM [26日$B3:G103] union all select * FROM [27日$B3:G103] union all select * FROM [28日$B3:G103] union all select * FROM [29日$B3:G103] union all select * FROM [30日$B3:G103] union all select * FROM [31日$B3:G103]) group by 品名,分类


group by  是对集合函数进行分组

卖猪仔二号

万分感激，已经把我解救出来了。。。。继续努力学习。。。