create table SC(S# varchar2(10),C# varchar2(10),score float)   建表

insert into SC values('01' , '01' , 80);insert into SC values('01' , '02' , 90);insert into SC values('01' , '03' , 99);insert into SC values('02' , '01' , 70);insert into SC values('02' , '02' , 60);insert into SC values('02' , '03' , 80);insert into SC values('03' , '01' , 80);insert into SC values('03' , '02' , 80);insert into SC values('03' , '03' , 80);insert into SC values('04' , '01' , 50);insert into SC values('04' , '02' , 30);insert into SC values('04' , '03' , 20);insert into SC values('05' , '01' , 76);insert into SC values('05' , '02' , 87);insert into SC values('06' , '01' , 31);insert into SC values('06' , '03' , 34);insert into SC values('07' , '02' , 89);insert into SC values('07' , '03' , 98);commit;

 插入数据

运行：

select s# ,max(case c# when '01' then score else 0 end) '01',max(case c# when '02' then score else 0 end)  '02'，max(case c# when '03' then score else 0 end)  '03'，avg(score)from scgroup by s#order by avg(score) desc

 报错

经过试验  如果列别名开头有数字的话  要用双引号引起来才不会报错

要这样：

select s# ,max(decode(C#,'01',score,0))"01",

max(decode(C#,'02',score,0))  "02"，

max(decode(C#,'03',score,0))  "03"，

avg(score)

from sc

group by s#

order by avg(score) desc