---方法1

select decode(deptno,10,'财务部',20,'研发部',30,'销售部','未知部门') dept,round(avg(sal),0),round(avg(nvl(comm,0)),0),case?

when avg(nvl(comm,0))>300 then '奖金不错'

when avg(nvl(comm,0))>100 and avg(nvl(comm,0))<300 then '奖金一般'

when avg(nvl(comm,0))<100 then '几乎没奖金'?

end status?

from emp

group by deptno?

order by deptno desc,avg(sal) desc;

?

---方法2

select decode(deptno,10,'财务部',20,'研发部',30,'销售部','未知部门') dept,round(avg(sal),0),round(avg(nvl(comm,0)),0),

decode(

?sign(round(avg(nvl(comm,0)),0)-300),

? ? ? ?1,'奖金不错',

? ? ? ?0,'奖金不错',

? ? ? ?-1,decode(

? ? ? ? ? ? ?sign(round(avg(nvl(comm,0)),0)-100),

? ? ? ? ? ? ? ?1,'奖金一般',

? ? ? ? ? ? ? ?0,'奖金一般',

? ? ? ? ? ? ? ?-1,'几乎没有奖金'

? ? ? ? ? ? ?)

)

as status from emp

group by deptno

order by deptno desc,avg(sal) desc;

?