求问SAS ADV 63 Q6

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

哈哈，如题～题目如下：
The table WORK.PILOTS contains the following data:
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;

麻烦大家解释一下B C D选项～
谢谢！

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：summarized statement following summarize Statemen following generate average include result