ORACLE ÐÐתÁÐ ²âÊÔͨ¹ý£¨9i£©

--ORACLE ÐÐתÁР ²âÊÔͨ¹ý£¨9i£©
/***************************************************************************************************
ÒÔѧÉú³É¼¨ÎªÀý×Ó£¬±È½ÏÐÎÏóÒ׶®
***********************************************************************************************************/
--1¡¢ÐÐתÁÐ
--> --> Éú³É²âÊÔÊý¾Ý
drop table Class ;
Create table Class(Student varchar2(20),Course varchar2(20),Score number) ;
Insert into Class
(
select 'ÕÅÈý','ÓïÎÄ',78 from dual union all
select 'ÕÅÈý','Êýѧ',87 from dual union all
select 'ÕÅÈý','Ó¢Óï',82 from dual union all
select 'ÕÅÈý','ÎïÀí',90 from dual union all
select 'ÀîËÄ','ÓïÎÄ',65 from dual union all
select 'ÀîËÄ','Êýѧ',77 from dual union all
select 'ÀîËÄ','Ó¢Óï',65 from dual union all
select 'ÀîËÄ','ÎïÀí',85 from dual );
--case ·½Ê½:
select Student,sum(ÓïÎÄ) ÓïÎÄ,sum(Êýѧ) Êýѧ,sum(ÎïÀí) ÎïÀí,sum(Ó¢Óï) Ó¢Óï
 from(
       select  Student,       
    case Course when 'ÓïÎÄ' then Score else 0 end as ÓïÎÄ,
    case Course when 'Êýѧ' then Score else 0 end as Êýѧ,
    case Course when 'ÎïÀí' then Score else 0 end as ÎïÀí,
    case Course when 'Ó¢Óï' then Score else 0 end as Ó¢Óï
from  Class )
group by Student;
--decode ·½Ê½:
select student,sum(decode(Course,'ÓïÎÄ', Score,0)) as ÓïÎÄ,
sum(decode(Course,'Êýѧ', Score,0)) as Êýѧ,
sum(decode(Course,'ÎïÀí', Score,0)) as ÎïÀí,
sum(decode(Course,'Ó¢Óï', Score,0)) as Ó¢Óï
from Class
group by student;
drop table Class ;