分享学习经验、使用技巧及收获感悟帖

wshcw

肉兄你真棒!E起加油!

cupworld

8楼的方法不怎么好用啊？

wjzwang

如上图所示,通过SQL得到右边的汇总,下面三个语句当中哪个正确?

大家可以先想几分钟，再往下看。

一

SELECT 产品大类,品类,SUM(数量) AS 数量,SUM(总金额) AS 总金额,总金额/数量 AS 均价 FROM [Sheet1$A:H] GROUP BY 产品大类,品类

二

SELECT 产品大类,品类,SUM(数量) AS 数量,SUM(总金额) AS 总金额,SUM(总金额) / SUM(数量) AS 均价 FROM [Sheet1$A:H] GROUP BY 产品大类,品类

三

SELECT 产品大类,品类,SUM(数量) AS 总数量,SUM(总金额) AS 总销售金额,总销售金额/总数量 AS 均价 FROM [Sheet1$A:H] GROUP BY 产品大类,品类

这三句的区别是”均价”生成的写法。

“均价”是通过计算得到的字段,在数据源里并不存在,以行一个语句为例,在得到它之前,先检测源数据表中有没有”总金额”和”数量”这两个字段，如有，则后续引用这个字段的计算以原有的字段为准，如没有，则检索别名中有没有”总金额”和”数量”，如有，才再以别名进行计算，假如连别名中也找不到”总金额”和”数量”，那么再提示”参数不足，期待为N”，N为找不到的个数。

这样看来好像三句都没错的，实际上，正确的是第二、第三句，虽然原字段中有”总金额”和”数量”这两个字段，但SEELECT语中用了GROUP BY进行分组了，导致数据无法生成，如果把第一句中的

总金额/数量 AS 均价

改为

SUM(总金额 / 数量) AS 均价

则可以生成所需的表，但均价的结果显然是错误的。

所以在用SQL进行统计时，特别是引用一些在语句中通过计算生成的字段时，要注意所用别名和数据源原有字段有所区别，以方便后续的引用。

感谢wuxiang_123版主提供帮忙，让我明白了这一点。

hjj0451

哇，楼主在这里开课了，刚看到，学习学习！

Richardzxb

留下记号慢慢学习，这种学习经验分享最实用了，多谢楼主

chen_pinghui

wjzwang 发表于 2010-1-5 10:06
昨天又一次中招了,在EXCLE文件里自动生成RESULTS.XLS,是一个"宏病毒",它让每个打开的EXCEL文件生成一个RESU ...

今天刚好用到这一招，真是感谢！

wjzwang

如图,现需根据"汇总"表中的餐段筛选"数据源"表里的数据,得出结果,最先想到的是通过UNION ALL连接早餐至特晚共6个字段的数据,重组一个新的数据源,如下:

SELECT 部门,工号,姓名 FROM
(SELECT '早餐' AS 筛选,部门,工号,姓名,早餐 AS 值 FROM [数据源$]
UNION ALL
SELECT '中餐',部门,工号,姓名,中餐 FROM [数据源$]
UNION ALL
SELECT '晚餐',部门,工号,姓名,晚餐 FROM [数据源$]
UNION ALL
SELECT '夜宵',部门,工号,姓名,夜宵 FROM [数据源$]
UNION ALL
SELECT '特中',部门,工号,姓名,特中 FROM [数据源$]
UNION ALL
SELECT '特晚',部门,工号,姓名,特晚 FROM [数据源$]),[汇总$A2:A3]
WHERE 值>0 AND 筛选=餐段
ORDER BY 部门,工号

但每多一个餐段,就得多一个UNION,感觉不够灵活,于是想能不能在WHERE 时就作出判断,得出筛选的餐段,从而得到正确的WHERE字段?
经过wuxiang_123(吴干事)的指点,学到了SWITCH函数,从而简化了SELECT语句,如下:

SELECT 部门,工号,姓名 FROM [数据源$],[汇总$A2:A3] WHERE SWITCH(餐段='早餐',早餐,餐段='中餐',中餐,餐段='晚餐',晚餐,餐段='夜宵',夜宵,餐段='特中',特中,餐段='特晚',特晚) is not null

特此留个记号,以便日后忘记!

1. 
   
2. 吴版说,因为数据源里,各餐段的数据并没有出现0,所以上面的SWITCH语句中"is not null"可以省去不用.
   
3. 
   
4. 除了SWITCH外,CHOOSE+INSTR也可以实现这一题的要求,如下:
   
5. 
   
6. SELECT 部门,工号,姓名 FROM [数据源$],[汇总$A2:A3] WHERE CHOOSE(INSTR("早餐中餐晚餐夜宵特中特晚",餐段)/2+1,早餐,中餐,晚餐,夜宵,特中,特晚)
   
7. 
   
8. 只是有个限制,只适用于餐段不名称是两个字,且组合起来没有重复的情况
   

复制代码

wjzwang

在透视里汇总多门店多产品的销量时,通过筛选很容易得到销量排前N位大的产品,不过如果直接用SELECT语句得到相同的结果时,好像不是那么简单了.
感觉运行起来有点慢.

SELECT * FROM
(SELECT 门店名称,商品型号,SUM(计算金额) AS 总销量 FROM [数据源$A7:O921]  GROUP BY 门店名称,商品型号) A
WHERE 商品型号 IN
(SELECT TOP 3 商品型号 FROM
(SELECT 门店名称,商品型号,SUM(计算金额) AS 总销量 FROM [数据源$A7:O921] GROUP BY 门店名称,商品型号) B
WHERE A.门店名称 = B.门店名称 ORDER BY 总销量 DESC)
ORDER BY 门店名称,总销量 DESC

rifl

有些内容很实用，慢慢学习

盈在你家

wjzwang 发表于 2009-9-14 14:21
每个月都要对数据进行加工处理，整理成符合统计的格式，其中一个就是提取产品型号，如“吉德洗衣机55-2288G ...

好东西，佩服。