[第45期]数据透视表水平测试(已总结)

BIN_YANG168

1.答题前请先阅读最新规则:正式竞赛区运行规则说明
2.请勿在跟贴中直接发答案(跟帖中如附答案一律不得分).
3.答案附件请以标准文件名格式发送至我的邮箱：bin_yang168@126.com

[ 本帖最后由 BIN_YANG168 于 2009-3-26 21:33 编辑 ]

jssy

发帖占位，不知能否达到小学毕业的水平？
导入外部数据+OLE DB查询＋SQL语句+创建数据透视表

1. select "2007","旗舰1店" as 4S店,车型,[1季度销量],[2季度销量],[3季度销量],[4季度销量],mid([发动机号码-维修代码],1,11),mid([发动机号码-维修代码],13,6) from `c:\竞赛基础题\2007年`.`旗舰1店$` union all
   
2. select "2007","旗舰2店" as 4S店,车型,0 as [1季度销量],0 as [2季度销量],0 as [3季度销量],[4季度销量],mid([发动机号码-维修代码],1,11),mid([发动机号码-维修代码],13,6) from `c:\竞赛基础题\2007年`.`旗舰2店$` union all
   
3. select "2008","旗舰3店" as 4S店,车型,[1季度销量],[2季度销量],[3季度销量],0 as [4季度销量],mid([发动机号码-维修代码],1,11),mid([发动机号码-维修代码],13,6) from `c:\竞赛基础题\2008年`.`旗舰3店$` union all
   
4. select "2008","旗舰4店" as 4S店,车型,0 as [1季度销量],[2季度销量],0 as [3季度销量],[4季度销量],mid([发动机号码-维修代码],1,11),mid([发动机号码-维修代码],13,6) from `c:\竞赛基础题\2008年`.`旗舰4店$`
   

复制代码

[ 本帖最后由 BIN_YANG168 于 2009-3-23 14:35 编辑 ]

dsp5000

分字段还没想好，先站位吧
sorry , 最近事太多了。忘了做了。

[ 本帖最后由 dsp5000 于 2009-3-25 08:25 编辑 ]

jyhxr

先抢个位置,有时间就做
外部数据源创建数据透视表+新建数据库查询＋Microsoft Query+SQL语句

1. SELECT '2007' , '旗舰1店' , `旗舰1店$`.车型, left("发动机号码-维修代码",11) , right("发动机号码-维修代码",6) , `旗舰1店$`.`1季度销量` , `旗舰1店$`.`2季度销量` , `旗舰1店$`.`3季度销量` , `旗舰1店$`.`4季度销量`   FROM `c:\xunlei\竞赛题\透视表\2007年`.`旗舰1店$` `旗舰1店$`   union all  (SELECT '2007'  , '旗舰2店' , `旗舰2店$`.车型, left("发动机号码-维修代码",11) , right("发动机号码-维修代码",6) , 0  , 0  , 0 , `旗舰2店$`.`4季度销量`   FROM `c:\xunlei\竞赛题\透视表\2007年`.`旗舰2店$` `旗舰2店$`)  union all  SELECT '2008' , '旗舰3店' , `旗舰3店$`.车型, left("发动机号码-维修代码",11) , right("发动机号码-维修代码",6) , `旗舰3店$`.`1季度销量` , `旗舰3店$`.`2季度销量` , `旗舰3店$`.`3季度销量` , 0  FROM `c:\xunlei\竞赛题\透视表\2008年`.`旗舰3店$` `旗舰3店$`   union all  (SELECT '2008'  , '旗舰4店' , `旗舰4店$`.车型, left("发动机号码-维修代码",11) , right("发动机号码-维修代码",6) , 0,`旗舰4店$`.`2季度销量`  , 0 , `旗舰4店$`.`4季度销量`   FROM `c:\xunlei\竞赛题\透视表\2008年`.`旗舰4店$` `旗舰4店$`)

复制代码

[ 本帖最后由 BIN_YANG168 于 2009-3-23 14:40 编辑 ]

yagi2008

基本完成，已经发邮件了~~~
外部数据源创建数据透视表+新建数据库查询＋Microsoft Query+SQL语句

1. SELECT 2007 as [年份],'旗舰1店' as [4S店名],`车型`,left(`发动机号码-维修代码`,11) as [发动机号码],right(`发动机号码-维修代码`,6) as [维修代码],`1季度销量`,`2季度销量`,`3季度销量`, `4季度销量` FROM `D:\竞赛基础题\2007年`.`旗舰1店$` `旗舰1店$` union all
   
2. SELECT 2007 as [年份],'旗舰2店' as [4S店名],`车型`,left(`发动机号码-维修代码`,11) as [发动机号码],right(`发动机号码-维修代码`,6) as [维修代码],0 as `1季度销量`,0 as `2季度销量`,0 as `3季度销量`,0 as `4季度销量` FROM `D:\竞赛基础题\2007年`.`旗舰2店$` `旗舰2店$` union all
   
3. SELECT 2008 as [年份],'旗舰3店' as [4S店名],`车型`,left(`发动机号码-维修代码`,11) as [发动机号码],right(`发动机号码-维修代码`,6) as [维修代码],`1季度销量`,`2季度销量`,`3季度销量`,0 as `4季度销量` FROM `D:\竞赛基础题\2008年`.`旗舰3店$` `旗舰3店$` union all
   
4. SELECT 2008 as [年份],'旗舰4店' as [4S店名],`车型`,left(`发动机号码-维修代码`,11) as [发动机号码],right(`发动机号码-维修代码`,6) as [维修代码],0 as `1季度销量`,`2季度销量`,0 as `3季度销量`, `4季度销量` FROM `D:\竞赛基础题\2008年`.`旗舰4店$` `旗舰4店$`
   

复制代码

SQL语句错误造成整个结果不正确，不得分。正确代码如下：

1. SELECT 2007 as [年份],'旗舰1店' as [4S店名],`车型`,left(`发动机号码-维修代码`,11) as [发动机号码],right(`发动机号码-维修代码`,6) as [维修代码],`1季度销量`,`2季度销量`,`3季度销量`, `4季度销量` FROM `D:\竞赛基础题\2007年`.`旗舰1店$` `旗舰1店$` union all
   
2. SELECT 2007 as [年份],'旗舰2店' as [4S店名],`车型`,left(`发动机号码-维修代码`,11) as [发动机号码],right(`发动机号码-维修代码`,6) as [维修代码],0 as `1季度销量`,0 as `2季度销量`,0 as `3季度销量`,`4季度销量` FROM `D:\竞赛基础题\2007年`.`旗舰2店$` `旗舰2店$` union all
   
3. SELECT 2008 as [年份],'旗舰3店' as [4S店名],`车型`,left(`发动机号码-维修代码`,11) as [发动机号码],right(`发动机号码-维修代码`,6) as [维修代码],`1季度销量`,`2季度销量`,`3季度销量`,0 as `4季度销量` FROM `D:\竞赛基础题\2008年`.`旗舰3店$` `旗舰3店$` union all
   
4. SELECT 2008 as [年份],'旗舰4店' as [4S店名],`车型`,left(`发动机号码-维修代码`,11) as [发动机号码],right(`发动机号码-维修代码`,6) as [维修代码],0 as `1季度销量`,`2季度销量`,0 as `3季度销量`, `4季度销量` FROM   `D:\竞赛基础题\2008年`.`旗舰4店$` `旗舰4店$`
   

复制代码

[ 本帖最后由 BIN_YANG168 于 2009-3-23 15:15 编辑 ]

qygszlb

郁闷了,我是分字符串搞定了,用工作薄名和工作表名作页字段这卡住了,充电去!

shlzml

已发给楼主邮件
占位
导入外部数据+新建数据库查询＋Microsoft Query+SQL语句+创建数据透视表

1. SELECT '2007'  ,'旗舰1店' ,`旗舰1店$`.车型,
   
2. left(`旗舰1店$`.`发动机号码-维修代码`,instr(1,`旗舰1店$`.`发动机号码-维修代码`,'-',1)-1),
   
3. right(`旗舰1店$`.`发动机号码-维修代码`,len(`旗舰1店$`.`发动机号码-维修代码`)-instr(1,`旗舰1店$`.`发动机号码-维修代码`,'-',1)),
   
4. `旗舰1店$`.`1季度销量`, `旗舰1店$`.`2季度销量`, `旗舰1店$`.`3季度销量`, `旗舰1店$`.`4季度销量`
   
5. FROM `D:\竞赛基础题\2007年`.`旗舰1店$` `旗舰1店$`
   
6. union all
   
7. SELECT '2007' ,'旗舰2店' ,`旗舰2店$`.车型,
   
8. left(`旗舰2店$`.`发动机号码-维修代码`,instr(1,`旗舰2店$`.`发动机号码-维修代码`,'-',1)-1),
   
9. right(`旗舰2店$`.`发动机号码-维修代码`,len(`旗舰2店$`.`发动机号码-维修代码`)-instr(1,`旗舰2店$`.`发动机号码-维修代码`,'-',1)),
   
10. 0 , 0 , 0 , `旗舰2店$`.`4季度销量`
    
11. FROM `D:\竞赛基础题\2007年`.`旗舰2店$` `旗舰2店$`
    
12. union all
    
13. SELECT '2008' ,'旗舰3店' ,`旗舰3店$`.车型,
    
14. left(`旗舰3店$`.`发动机号码-维修代码`,instr(1,`旗舰3店$`.`发动机号码-维修代码`,'-',1)-1),
    
15. right(`旗舰3店$`.`发动机号码-维修代码`,len(`旗舰3店$`.`发动机号码-维修代码`)-instr(1,`旗舰3店$`.`发动机号码-维修代码`,'-',1)),
    
16. `旗舰3店$`.`1季度销量`, `旗舰3店$`.`2季度销量`, `旗舰3店$`.`3季度销量`, 0
    
17. FROM `D:\竞赛基础题\2008年`.`旗舰3店$` `旗舰3店$`
    
18. union all
    
19. SELECT '2008' ,'旗舰4店' ,`旗舰4店$`.车型,
    
20. left(`旗舰4店$`.`发动机号码-维修代码`,instr(1,`旗舰4店$`.`发动机号码-维修代码`,'-',1)-1),
    
21. right(`旗舰4店$`.`发动机号码-维修代码`,len(`旗舰4店$`.`发动机号码-维修代码`)-instr(1,`旗舰4店$`.`发动机号码-维修代码`,'-',1)),
    
22. 0 , `旗舰4店$`.`2季度销量`, 0 , `旗舰4店$`.`4季度销量`
    
23. FROM `D:\竞赛基础题\2008年`.`旗舰4店$` `旗舰4店$`
    

复制代码

[ 本帖最后由 BIN_YANG168 于 2009-3-23 14:54 编辑 ]

yuwd3

研究了N多时间，总算会了。
占位
外部数据源创建数据透视表+新建数据库查询＋Microsoft Query+SQL语句：

1. SELECT 车型, `发动机号码-维修代码`, `1季度销量`, `2季度销量`, `3季度销量`, `4季度销量`,2007,'旗舰1店',left(`发动机号码-维修代码`,11),right(`发动机号码-维修代码`,6)
   
2. FROM `D:\My Documents\excel\透视表\竞赛基础题\2007年`.`旗舰1店$` `旗舰1店$` union all
   
3. SELECT 车型, `发动机号码-维修代码`, 0,0,0, `4季度销量`,2007,'旗舰2店',left(`发动机号码-维修代码`,11),right(`发动机号码-维修代码`,6)
   
4. FROM `D:\My Documents\excel\透视表\竞赛基础题\2007年`.`旗舰2店$` `旗舰2店$` union all
   
5. SELECT 车型, `发动机号码-维修代码`, `1季度销量`, `2季度销量`, `3季度销量`, 0,2008,'旗舰3店',left(`发动机号码-维修代码`,11),right(`发动机号码-维修代码`,6)
   
6. FROM `D:\My Documents\excel\透视表\竞赛基础题\2008年`.`旗舰3店$` `旗舰3店$` union all
   
7. SELECT 车型, `发动机号码-维修代码`, 0, `2季度销量`, 0, `4季度销量`,2008,'旗舰4店',left(`发动机号码-维修代码`,11),right(`发动机号码-维修代码`,6)
   
8. FROM `D:\My Documents\excel\透视表\竞赛基础题\2008年`.`旗舰4店$` `旗舰4店$`
   

复制代码

[ 本帖最后由 BIN_YANG168 于 2009-3-23 14:59 编辑 ]

Scarlett_88

已发邮件，请查收。
外部数据源创建数据透视表+新建数据库查询＋Microsoft Query+SQL语句：

1. SELECT expr1000 as 年份, expr1001 as 4s店名, 车型, expr1003 as 发动机号码, expr1004 as 维修代码,expr1005 as 1季度销量, expr1006 as 2季度销量, expr1007 as 3季度销量, expr1008 as 4季度销量
   
2. from (SELECT '2007', '旗舰1店',`旗舰1店$`.车型, left(`旗舰1店$`.`发动机号码-维修代码`,11), right(`旗舰1店$`.`发动机号码-维修代码`,6), (`旗舰1店$`.`1季度销量`)*1, (`旗舰1店$`.`2季度销量`)*1, (`旗舰1店$`.`3季度销量`)*1, (`旗舰1店$`.`4季度销量`)*1
   
3. FROM `D:\竞赛基础题\2007年`.`旗舰1店$` `旗舰1店$`
   
4. union all
   
5. SELECT '2007', '旗舰2店', `旗舰2店$`.车型, left(`旗舰2店$`.`发动机号码-维修代码`,11), right(`旗舰2店$`.`发动机号码-维修代码`,6), null, null, null, (`旗舰2店$`.`4季度销量`)*1
   
6. FROM `D:\竞赛基础题\2007年`.`旗舰2店$` `旗舰2店$`
   
7. union all
   
8. SELECT '2008', '旗舰3店',`旗舰3店$`.车型, left(`旗舰3店$`.`发动机号码-维修代码`,11), right(`旗舰3店$`.`发动机号码-维修代码`,6), (`旗舰3店$`.`1季度销量`)*1, (`旗舰3店$`.`2季度销量`)*1, (`旗舰3店$`.`3季度销量`)*1, null
   
9. FROM `D:\竞赛基础题\2008年`.`旗舰3店$` `旗舰3店$`
   
10. union all
    
11. SELECT '2008', '旗舰4店', `旗舰4店$`.车型, left(`旗舰4店$`.`发动机号码-维修代码`,11), right(`旗舰4店$`.`发动机号码-维修代码`,6), null, (`旗舰4店$`.`2季度销量`)*1, null, (`旗舰4店$`.`4季度销量`)*1
    
12. FROM `D:\竞赛基础题\2008年`.`旗舰4店$` `旗舰4店$`)
    

复制代码

[ 本帖最后由 BIN_YANG168 于 2009-3-23 14:59 编辑 ]

xowi99

答案已发送，占位。
外部数据源创建数据透视表+新建数据库查询＋Microsoft Query+SQL语句：

1. SELECT expr1000 as 年份, expr1001 as 4s店名, 车型, expr1003 as 发动机号码, expr1004 as 维修代码,expr1005 as 1季度销量, expr1006 as 2季度销量, expr1007 as 3季度销量, expr1008 as 4季度销量 from (SELECT '2007', '旗舰1店',`车型`, left(`发动机号码-维修代码`,11), right(`发动机号码-维修代码`,6), --(`1季度销量`), --(`2季度销量`), --(`3季度销量`), --(`4季度销量`) FROM `E:\xowi99_45数据透视表\2007年`.`旗舰1店$`  union all SELECT '2007', '旗舰2店', `旗舰2店$`.车型, left(`发动机号码-维修代码`,11), right(`发动机号码-维修代码`,6), null, null, null, --(`4季度销量`) FROM `E:\xowi99_45数据透视表\2007年`.`旗舰2店$`  union all SELECT '2008', '旗舰3店',车型, left(`发动机号码-维修代码`,11), right(`发动机号码-维修代码`,6), --(`1季度销量`), --(`2季度销量`), --(`3季度销量`), null FROM `E:\xowi99_45数据透视表\2008年`.`旗舰3店$` union all SELECT '2008', '旗舰4店', 车型, left(`发动机号码-维修代码`,11), right(`发动机号码-维修代码`,6), null, --(`2季度销量`), null, --(`4季度销量`) FROM `E:\xowi99_45数据透视表\2008年`.`旗舰4店$` )

复制代码

[ 本帖最后由 BIN_YANG168 于 2009-3-23 15:01 编辑 ]