【数据透视表中级201301班】D组-第二讲作业上交贴（已开贴）

9lee

1、作业请直接用“添加代码文字”的方式提交
2、不要重复占楼，修改请在原楼层。
3、本帖已设置回复仅作者可见
4、非D组学员勿跟帖，违者扣分删帖
5、作业截止时间：2013-12-22 18:00

【数据透视表中级201301班】第二讲作业
http://www.excelpx.com/thread-315636-1-1.html

无影无形

1. <blockquote>
   

复制代码

D08：无影无形

1. 1   select  top  24 产品码 from [订单$] where 产品码 like 'FG%'
   
2. 
   
3. 
   
4. 
   
5. 2   select top 20 * from[订单$A1:G6044] where 客户="鑫豪专卖店"  order by 订单数量 desc
   
6. 
   
7. 
   
8. 3   select top 10 * from [订单$A1:G6044] where 款号 like '%L%' and 订单数量>2000 order by 订单数量
   
9. 
   
10. 
    
11. 4   select 客户,工单号,订单数量,订单交期 from [订单$a1:g6044] where 工单号 like '[c-z]%'  AND  订单交期 <=#2011-6-30# and 客户 in('火狐野营' , '徐州火凤凰户外运动' ,'昆明人在旅途' ,'卡迪克公司' )
    
12. 
    
13. 
    
14. 5   select * from [订单$A1:G6044] where [ERP CO号] like '%001' and 订单数量 >=1000 and 订单数量 <2000 and 订单交期 >=#2011-7-1#
    
15. 
    
16. 
    
17. 6   select 产品码,sum(订单数量) as 订单量 from (select 产品码,订单数量 from[订单$a1:g6044] where 订单数量>5000)  group by 产品码  order by sum(订单数量)
    
18. 
    
19. 
    
20. 7   select 工单号,count(*) as 订单次数,sum(订单数量) as 订单合计数 from [订单$a1:g6044] where 工单号 like 'A%' group by 工单号

复制代码

lasharks

作业1

1. select 产品码 from [订单$]

复制代码

作业2

1. select top 20 * from [订单$] where 客户='鑫豪专卖店' order by 订单数量 desc

复制代码

作业3

1. select top 10 * from [订单$] where 款号 like "%L%" and 订单数量>2000 order by 订单数量

复制代码

作业4

1. select 客户,工单号,订单数量,订单交期 from [订单$] where (订单交期 between #2011/1/1# and #2011/6/30#) and 客户 in ("昆明人在旅途","卡迪克公司","火狐野营","徐州火凤凰户外运动") and 工单号 like "[!AB]%"

复制代码

作业5

1. select * from [订单$] where 订单交期>=#2011/7/1# and [ERP CO号] like "%001" and 订单数量>=1000 and 订单数量<2000

复制代码

作业6

1. select 产品码,sum(订单数量) as 订单量 from [订单$] where 订单数量>5000 group by 产品码 order by sum(订单数量)

复制代码

作业7

1. select 工单号,count(*) as 订单次数,sum(订单数量) as 订单合计数 from [订单$] where 工单号 like "A%" group by 工单号

复制代码

jerryken

1. select 产品码 from [订单$]

复制代码

2、

1. select top 20 * from [订单$a1:g6044] where 客户 like '鑫豪专卖店' order by 订单数量 desc

复制代码

3、

1. select top 10 * from [订单$a1:g6044] where 款号 like '%L%' and 订单数量 >2000 order by 订单数量 asc

复制代码

4、

1. select 客户,工单号,订单数量,订单交期 from [订单$a1:g6044] where 客户 in ("昆明人在旅途","卡迪克公司","火狐野营","徐州火凤凰户外运动") and 工单号 not like '%[a-b]%' and 订单交期  between #2011/1/1# and #2011/6/30#

复制代码

5、

1. select * from [订单$a1:g6044] where 订单交期 > #2011/6/30# and 订单数量 betweEn 1000 and  1999.99

复制代码

6、

1. select 产品码,sum(订单数量) as 订单量 from [订单$] where 订单数量 > 5000    group by 产品码  order by sum(订单数量) asc

复制代码

7、

1. select 工单号,count(*) as 订单次数,sum(订单数量) as 订单合计数   from [订单$]   where 工单号 like 'A%'   group by 工单号

复制代码

xiaoni

第一题:select 产品码 from [订单$]
每二题:select TOP 20 * FROM [订单$A:G] where 客户="鑫豪专卖店" order by 订单数量 DESC
第三题:SELECT TOP 10 * FROM [订单$a:g] where 款号 like '%L%' AND 订单数量>2000 order by 订单数量
第四题:select 客户,工单号,订单数量,订单交期 from [订单$] where 客户 IN("昆明人在旅途","卡迪克公司","火狐野营","徐州火凤凰户外运动") and
订单交期 between #2011-1-1# and #2011-6-30# AND 工单号 like '[!ab]%'
第五题:select * from [订单$a:g] where 订单交期>=#2011-7-1# AND 订单数量 >=1000 and 订单数量<2000 and right([ERP CO号],3)="001"
第六题:select  产品码,sum(订单数量) as 订单量 from [订单$] group by 产品码 having sum(订单数量)>50000 order by sum(订单数量)
第七题：SELECT 工单号,count(订单数量) as 订单次数,sum(订单数量) as 订单合计数 from [订单$] where 工单号 like '[a]%' group by 工单号

笨妖儿

看了两遍，只会
作业 一 select 产品码 from [订单$]

fuguer

1. <div class="blockcode"><blockquote><blockquote>select 产品码 from [订单$]
   
2. select top 20 * from [订单$] where 客户= "鑫豪专卖店" order by 订单数量 desc
   
3. select top 10 * from [订单$] where 订单数量 >2000 and 款号 like "%L%" order by 订单数量

复制代码

      select 产品码 from [订单$]
      select top 20 * from [订单$] where 客户= "鑫豪专卖店" order by 订单数量 desc
      select top 10 * from [订单$] where 订单数量 >2000 and 款号 like "%L%" order by 订单数量

      select 客户,工单号,订单数量,订单交期 from [订单$] where 订单交期 between #2011/1/1# and #2011/6/30# and 工单号 like "[!A-b]%" and 客户 in ("昆明人在旅途","卡迪克公司","火狐野营","徐州火凤凰户外运动")
     select * from [订单$] where 订单交期 >= #2011/7/1# and 订单数量 >=1000 and 订单数量 <2000
     select 产品码,sum(订单数量) as 订单量 from [订单$] group by 产品码



代码我修改了好几次，但每次提交后都发现没有保存，所以只有在下面贴解答了

as0810114

1

1. select 产品码 from [订单$]

复制代码

2

1. select top 20 * from [订单$] where 客户="鑫豪专卖店" order by 订单数量 desc

复制代码

3

1. select * from [订单$] where 款号 like "%L%" and 订单数量>2000 order by 订单数量

复制代码

4

1. select 客户,工单号,订单数量,订单交期 from [订单$] where 客户 in ("昆明人在旅途","卡迪克公司","火狐野营","徐州火凤凰户外运动") and 订单交期 between #2011-1-1# and #2011-6-30# and 工单号 not like "A%" and 工单号 not like "B%"

复制代码

5

1. select * from [订单$] where 订单交期 >= #2011-7-1# and [ERP CO号] like "%001" and 订单数量 >= 1000 and 订单数量 <2000

复制代码

6

1. select 产品码,sum(订单数量) as 订单合计 from [订单$] where 订单数量 > 5000 group by 产品码 order by sum(订单数量)

复制代码

7

1. select 工单号,count(*) as 订单次数,sum(订单数量) as 订单合计数 from [订单$]group by 工单号  having 工单号 like "A%"

复制代码

dezhi28

1. 作业1：SELECT 产品码 FROM [订单$]
   
2. 作业2：SELECT TOP 20 * FROM [订单$] WHERE 客户="鑫豪专卖店" ORDER BY 订单数量 DESC
   
3. 作业3：SELECT TOP 10 * FROM [订单$] WHERE 订单数量>2000 AND 款号 LIKE '%L%' ORDER BY 订单数量
   
4. 作业4：SELECT 客户,工单号,订单数量,订单交期 FROM [订单$] WHERE 订单交期 BETWEEN #2011-1-1# AND #2011-6-30# AND 客户 in ('昆明人在旅途','卡迪克公司','火狐野营','徐州火凤凰户外运动') AND 工单号 LIKE '[!A]%' AND 工单号 LIKE '[!B]%'
   
5. 作业5：SELECT * FROM [订单$] WHERE 订单交期 >= #2011-7-1# AND [ERP CO号] like '%001' AND 订单数量 BETWEEN 1000 AND 1999
   
6. 作业6：SELECT * FROM (SELECT 产品码,sum(订单数量) AS 订单量 FROM [订单$] GROUP BY 产品码) WHERE 订单量 >50000 ORDER BY 订单量
   
7. 作业7：SELECT 工单号,count(订单数量) AS 订单次数 ,sum(订单数量) AS 订单合计数 FROM [订单$] WHERE 工单号 like 'A%' GROUP BY 工单号

复制代码

不知对不对，请老师批阅。

ct126_ct

D01:ct126_ct