w******p
发帖数: 62 | 1 Item 6 of 63 Mark item for review Item 6 of 63 Mark item for review Item 6 of 63 Mark item for review Item 6 of 63 Mark item for review
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
;
我在SAS9.2上得到的结果是：
A，成功。
B，没结果但信息提示NOTE: The query as specified involves ordering by an item
that doesn't appear in its SELECT clause.。
C，ERROR: Subquery evaluated to more than one row.
D，ERROR: Ambiguous reference, column Avg is in more than one table.
到底哪个是正确答案呢？为什么？ |
|
