SAS中文论坛

标题: 跟crackman做sas adv认证试题（5） [打印本页]

作者: shiyiming 时间: 2010-11-16 15:01
标题: 跟crackman做sas adv认证试题（5）
From crackman's blog on Baidu

The table WORK.PILOTS contains the following data: 
 
   WORK.PILOTS 
 
   Id   Name     Jobcode Salary 
   --- ------   ------- ------ 
   001 Albert   PT1       50000 
   002 Brenda   PT1       70000 
   003 Carl     PT1       60000 
   004 Donna    PT2       80000 
   005 Edward   PT2       90000 
   006 Flora    PT3      100000 
 
The data set was summarized to include average 
salary based on jobcode:       
 
   Jobcode Salary     Avg      
   ------- ------   -----      
   PT1       50000   60000      
   PT1       70000   60000      
   PT1       60000   60000      
   PT2       80000   85000      
   PT2       90000   85000      
   PT3      100000 100000      
 
Which SQL statement could NOT generate 
this result? 
 
        A. 
select 
   Jobcode,   
   Salary,    
   avg(Salary) label='Avg'      
from WORK.PILOTS 
group by Jobcode 
order by Id   
;      
 
     B. 
select 
   Jobcode,   
   Salary,    
   (select avg(Salary) 
   from WORK.PILOTS as P1       
   where P1.Jobcode=P2.Jobcode) as Avg   
from WORK.PILOTS as P2 
order by Id   
;      
 
     C. 
select 
   Jobcode,   
   Salary,    
   (select avg(Salary) 
   from WORK.PILOTS     
   group by Jobcode) as Avg     
from WORK.PILOTS 
order by Id   
;      
 
     D. 
select 
   Jobcode,   
   Salary,    
   Avg 
from   
   WORK.PILOTS, 
 (select     
      Jobcode as Jc,    
      avg(Salary) as Avg 
   from WORK.PILOTS     
   group by 1) 
where Jobcode=Jc 
order by Id   
答案是C. 
本文属于博客：<a href="http://hi.baidu.com/crack_man/home"><a class="postlink" href="http://hi.baidu.com/crack_man/home">http://hi.baidu.com/crack_man/home</a></a> 版权归作者所有，欢迎转载！如有转载，请务必注明出处！未经
本文作者同意不得用于商业应用。
这道题考察的SQL语句的多个方面的知识和技巧。下面就每一个答案来做解析： 
A: 
select id,Jobcode,Salary,avg(Salary) label='Avg' from WORK.PILOTS 
group by Jobcode order by Id ; 
这条语句是利用SQL过程中的AVG函数，计算某一列的均值。本语句中，先按照JOBCODE分组计算SALARY的均值，然后按照
ID这个变量排序输出结果。ID的默认排序是升序， 
这个升序是作用了全部数据集，不是针对分组的排序的。 
关于是不是分组，可以有如下程序示例： 
data pilots; 
input id name $ jobcode $ salary@; 
datalines; 
   001 Albert   PT1       50000 
   002 Brenda   PT1       70000 
   003 Carl     PT1       60000 
   008 crack    PT1       100000 
   004 Donna    PT2       80000 
   005 Edward   PT2       90000 
   006 Flora    PT3       100000 
   ; 
   run; 
proc sql; 
select id, 
   Jobcode,   
   Salary,    
   avg(Salary) label='Avg'      
from WORK.PILOTS 
group by Jobcode 
/*先去掉这句话，然后加上这句话，看看结果 
order by Id 
*/ 
; 
quit; 
B: 
select Jobcode, Salary, (select avg(Salary) from WORK.PILOTS as P1 where P1.Jobcode=P2.Jobcode) as Avg 
from WORK.PILOTS as P2 order by Id ; 
执行时转化为下面语句： 
select P2.jobcode, P2.salary, 
               ( select AVG(P1.salary) 
                   from WORK.PILOTS P1 
                  where P1.jobcode = P2.jobcode 
               ) as Avg 
          from WORK.PILOTS P2 
      order by P2.id asc; 
这里利用的是自身的数据集，分别设置不同的别名，可以当做两个数据集来使用。不过这里WHERE语句很值得学习，我个
人对这里的WHERE语句这样理解的：(select avg(Salary) from WORK.PILOTS as P1 where P1.Jobcode=P2.Jobcode)启示
执行之后返回的是三个AVG均值，但是因为这三个AVG均值都是与JOBCODE匹配的 
，那么前面的select Jobcode, Salary，
所以当执行到(select avg(Salary) from WORK.PILOTS as P1 where P1.Jobcode=P2.Jobcode)这里时，实际上是选择了
select jobcode 中jobcode对应的avg均值。
C: 
select Jobcode,Salary,(select avg(Salary) from WORK.PILOTS group by Jobcode) as Avg     
from WORK.PILOTS 
order by Id ; 
其实C和B有点像，但是C是错误，因为C中“select avg(Salary) from WORK.PILOTS group by Jobcode”一次返回的不是
一个值，而是一组数值。
D: 
select Jobcode,Salary,Avg from WORK.PILOTS,(select Jobcode as Jc,avg(Salary) as Avg 
from WORK.PILOTS group by 1) 
where Jobcode=Jc 
order by Id ; 
D实际上是执行两个表的水平连接。第一个是POLOTS，另外一个是子查询得到返回结果。按照JOBCODE条件筛选出结果。 
按照ID升序排列。这里子查询中的GROUP BY 1等价于 GROUP BY JC.
 
示例程序：
data pilots; 
input id name $ jobcode $ salary@; 
datalines; 
   001 Albert   PT1       50000 
   002 Brenda   PT1       70000 
   003 Carl     PT1       60000 
   004 Donna    PT2       80000 
   005 Edward   PT2       90000 
   006 Flora    PT3       100000 
   ; 
   run; 
proc sql ; 
/* A */ 
select id, 
   Jobcode,   
   Salary,    
   avg(Salary) label='Avg'      
from WORK.PILOTS 
group by Jobcode 
order by Id 
;
/* B*/ 
select 
   Jobcode,   
   Salary,    
   (select avg(Salary) 
   from WORK.PILOTS as P1       
   where P1.Jobcode=P2.Jobcode) as Avg   
from WORK.PILOTS as P2 
order by Id 
; 
/* C */ 
select 
   Jobcode,   
   Salary,    
   (select avg(Salary) 
   from WORK.PILOTS     
   group by Jobcode) as Avg     
from WORK.PILOTS 
order by Id   
; 
 
/* D*/ 
select 
   Jobcode,   
   Salary,    
   Avg 
from   
   WORK.PILOTS, 
 (select     
      Jobcode as Jc,    
      avg(Salary) as Avg 
   from WORK.PILOTS     
   group by 1) 
where Jobcode=Jc 
order by Id   
; 
quit; 
 <a href="http://hi.baidu.com/crack%5Fman/blog/item/667f8ed2f61fe13f06088b0e.html">阅读全文</a>

 类别：<a href="http://hi.baidu.com/crack%5Fman/blog/category/%B8%FAcrackman%D7%F6sas%20adv%C8%CF%D6%A4%CA%D4%CC%E2">跟crackman做sas adv认证试题</a> <a href="http://hi.baidu.com/crack%5Fman/blog/item/667f8ed2f61fe13f06088b0e.html#comment">查看评论</a>

欢迎光临 SAS中文论坛 (https://mysas.net/forum/)