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

linpansheng

占个位置先，迟点再发答案
导入外部数据+OLE DB查询＋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 `E:\竞赛基础题\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 `E:\竞赛基础题\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 `E:\竞赛基础题\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 `E:\竞赛基础题\2008年`.`旗舰4店$` `旗舰4店$`)
    

复制代码

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

yoka

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

1. SELECT '2007'+'年' ,
   
2.        '旗舰1店' ,
   
3.        `旗舰1店$`.车型 ,
   
4.        mid(`旗舰1店$`.`发动机号码-维修代码`,1,11) ,
   
5.        mid(`旗舰1店$`.`发动机号码-维修代码`,13,6) ,
   
6.        `旗舰1店$`.`1季度销量` ,
   
7.        `旗舰1店$`.`2季度销量` ,
   
8.        `旗舰1店$`.`3季度销量` ,
   
9.        `旗舰1店$`.`4季度销量`
   
10. FROM `C:\Documents and Settings\361988\Desktop\竞赛基础题\2007年`.`旗舰1店$` `旗舰1店$`
    
11. UNION ALL
    
12. SELECT '2007'+'年',
    
13.        '旗舰2店',
    
14.        `旗舰2店$`.车型,
    
15.        mid(`旗舰2店$`.`发动机号码-维修代码`,1,11) ,
    
16.        mid(`旗舰2店$`.`发动机号码-维修代码`,13,6) ,
    
17.        NULL,
    
18.        NULL,
    
19.        NULL,
    
20.        `旗舰2店$`.`4季度销量`
    
21. FROM `C:\Documents and Settings\361988\Desktop\竞赛基础题\2007年`.`旗舰2店$` `旗舰2店$`
    
22. UNION ALL
    
23. SELECT '2008'+'年',
    
24.        '旗舰3店',
    
25.        `旗舰3店$`.车型,
    
26.        mid(`旗舰3店$`.`发动机号码-维修代码`,1,11) ,
    
27.        mid(`旗舰3店$`.`发动机号码-维修代码`,13,6) ,
    
28.        `旗舰3店$`.`1季度销量` ,
    
29.        `旗舰3店$`.`2季度销量` ,
    
30.        `旗舰3店$`.`3季度销量` ,
    
31.        NULL
    
32. FROM `C:\Documents and Settings\361988\Desktop\竞赛基础题\2008年`.`旗舰3店$` `旗舰3店$`
    
33. UNION ALL
    
34. SELECT '2008'+'年',
    
35.        '旗舰4店',
    
36.        `旗舰4店$`.车型,
    
37.        mid(`旗舰4店$`.`发动机号码-维修代码`,1,11) ,
    
38.        mid(`旗舰4店$`.`发动机号码-维修代码`,13,6) ,
    
39.        NULL,
    
40.        `旗舰4店$`.`2季度销量` ,
    
41.        NULL,
    
42.        `旗舰4店$`.`4季度销量`
    
43. FROM `C:\Documents and Settings\361988\Desktop\竞赛基础题\2008年`.`旗舰4店$` `旗舰4店$`
    

复制代码

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

水星钓鱼

yoka不等我，呵呵。占位

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

toby08

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

1. 导入外部数据+新建数据库查询＋Microsoft Query+SQL语句+创建数据透视表
   
2. SELECT '2007年'as 年份,'旗舰1店'as 4S店名,车型,left([发动机号码-维修代码],11) as 发动机号码,right([发动机号码-维修代码],6) as 维修代码,[1季度销量],[2季度销量],[3季度销量],[4季度销量]
   
3. FROM `C:\Documents and Settings\Administrator\桌面\2007年`.`旗舰1店$` `旗舰1店$`
   
4. UNION ALL SELECT '2007年'as 年份,'旗舰2店'as 4S店名,车型,left([发动机号码-维修代码],11) as 发动机号码,right([发动机号码-维修代码],6) as 维修代码,0 as 1季度销量,0 as 2季度销量,0 as 3季度销量,[4季度销量]
   
5. FROM `C:\Documents and Settings\Administrator\桌面\2007年`.`旗舰2店$` `旗舰2店$`
   
6. UNION ALL SELECT '2008年'as 年份,'旗舰3店'as 4S店名,车型,left([发动机号码-维修代码],11) as 发动机号码,right([发动机号码-维修代码],6) as 维修代码,[1季度销量],[2季度销量],[3季度销量],0 as 4季度销量
   
7. FROM `C:\Documents and Settings\Administrator\桌面\2008年`.`旗舰3店$` `旗舰3店$`
   
8. UNION ALL SELECT '2008年'as 年份,'旗舰4店'as 4S店名,车型,left([发动机号码-维修代码],11) as 发动机号码,right([发动机号码-维修代码],6) as 维修代码,0 as 1季度销量,[2季度销量],0 as 3季度销量,[4季度销量]
   
9. FROM `C:\Documents and Settings\Administrator\桌面\2008年`.`旗舰4店$` `旗舰4店$`
   

复制代码

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

hxhgxy

呵呵，应该说是SQL水平测试才对。
已经从园子里找到答案了。

neverquest

此题很实用，强！
邮件已发，占位

导入外部数据+OLE DB查询＋SQL语句+创建数据透视表：

1. select "2007年" as `年份`,* from (SELECT "旗舰1店$" as `4S店名`,`车型`, left(`旗舰1店$`.`发动机号码-维修代码`,11) as `发动机号码`, right(`旗舰1店$`.`发动机号码-维修代码`,6) as `维修代码`,`1季度销量`,`2季度销量`, `3季度销量`,`4季度销量`
   
2. FROM `D:\竞赛基础题\2007年`.`旗舰1店$`
   
3. UNION ALL
   
4. SELECT "旗舰2店$" as `4S店名`, `车型`, left(`旗舰2店$`.`发动机号码-维修代码`,11) as `发动机号码`, right(`旗舰2店$`.`发动机号码-维修代码`,6) as `维修代码`,0,0,0, `4季度销量`
   
5. FROM `D:\竞赛基础题\2007年`.`旗舰2店$`)
   
6. UNION ALL
   
7. select "2008年" as `年份`,* from (SELECT "旗舰3店$" as `4S店名`, `车型`, left(`旗舰3店$`.`发动机号码-维修代码`,11) as `发动机号码`, right(`旗舰3店$`.`发动机号码-维修代码`,6) as `维修代码`, `1季度销量`,`2季度销量`,`3季度销量`, 0
   
8. FROM `D:\竞赛基础题\2008年`.`旗舰3店$`
   
9. UNION ALL
   
10. SELECT "旗舰4店$" as `4S店名`,`车型`, left(`旗舰4店$`.`发动机号码-维修代码`,11) as `发动机号码`, right(`旗舰4店$`.`发动机号码-维修代码`,6) as `维修代码`, 0, `2季度销量`, 0, `4季度销量`
    
11. FROM `D:\竞赛基础题\2008年`.`旗舰4店$`)
    

复制代码

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

yangjia1980

答案已发，跑进来占个座
导入外部数据+新建数据库查询＋Microsoft Query+SQL语句+创建数据透视表：

1. SELECT 2007 ,1 , a.`1季度销量`, a.`2季度销量`, a.`3季度销量`, a.`4季度销量`, a.车型, left(a.`发动机号码-维修代码`,11),right(a.`发动机号码-维修代码`,6) FROM `d:\work\2007年.xls`.`旗舰1店$` a union SELECT 2007 ,2 , 0 , 0 , 0 , b.`4季度销量`, b.车型, left(b.`发动机号码-维修代码`,11),right(b.`发动机号码-维修代码`,6) FROM `d:\work\2007年.xls`.`旗舰2店$` b union SELECT 2008 ,3 , c.`1季度销量`, c.`2季度销量`, c.`3季度销量`, 0 , c.车型, left(c.`发动机号码-维修代码`,11),right(c.`发动机号码-维修代码`,6) FROM `d:\work\2008年.xls`.`旗舰3店$` c union SELECT 2008 ,4 , 0 , d.`2季度销量`, 0 , d.`4季度销量`, d.车型, left(d.`发动机号码-维修代码`,11),right(d.`发动机号码-维修代码`,6) FROM `d:\work\2008年.xls`.`旗舰4店$` d

复制代码

页字段“4S店名”数据项名称与答题演示不符，后期也未进行修改，不得分！

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

BIN_YANG168

我的答案,导入外部数据+OLE DB查询＋SQL语句+创建数据透视表,请将附件解压缩到D盘根目录下：

1. SELECT "2007年", * FROM (SELECT "旗舰1店",车型, LEFT([发动机号码-维修代码],11),RIGHT([发动机号码-维修代码],6) , [1季度销量],[2季度销量],[3季度销量],[4季度销量] from [D:\竞赛基础题\2007年`.`旗舰1店$] union all  SELECT "旗舰2店",车型, LEFT([发动机号码-维修代码],11),RIGHT([发动机号码-维修代码],6) ,0 as [1季度销量],0 as [2季度销量],0 as [3季度销量],[4季度销量] from [D:\竞赛基础题\2007年`.`旗舰2店$]) UNION ALL SELECT "2008年", * FROM (SELECT "旗舰3店",车型, LEFT([发动机号码-维修代码],11),RIGHT([发动机号码-维修代码],6) ,[1季度销量],[2季度销量],[3季度销量],0 as [4季度销量]  from [D:\竞赛基础题\2008年`.`旗舰3店$] UNION ALL SELECT "旗舰4店",车型, LEFT([发动机号码-维修代码],11),RIGHT([发动机号码-维修代码],6),0 as [1季度销量],[2季度销量],0 as [3季度销量],[4季度销量]  from [D:\竞赛基础题\2008年`.`旗舰4店$])

复制代码

演示文件：

另外一种创建方法的演示：（借用YOKA的SQL代码）


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

lbzcc

杨兄 透视表的应用学问可真大
有空可否在此贴顺便给我们大家介绍一下： 诸如此类在创建透视表时的所需要用的的
SQL语句的详细介绍 比如语法 规则什么的（这方面我是个菜鸟，不知道这样说对不对）
还望杨兄不吝赐教！

linpansheng

出多几次这样的题目，估计就会学好SQL在透视表的应用了