行列转换——oracle

行列转换，同一种效果两种方法

select  t5.姓名,sum(t5.HTML) as HTML,sum(t5.JSP) as JSP,sum(t5.SQL) asSQL,sum(t5.Java) asJava,sum(t5.总分) as总分,sum(t5.平均分) as平均分 ,sum(rownum) as总分排名from(

select t0.z as姓名, sum(t0.a)as HTML,sum(t0.b) as JSP,sum(t0.c)asSQL,sum(t0.d) asJava,sum(t0.a)+sum(t0.b)+sum(t0.c)+sum(t0.d) as总分,(sum(t0.a)+sum(t0.b)+sum(t0.c)+sum(t0.d))/4as平均分from(

selectt1.stu_name as z,t1.score as a,0as b,0as c,0as d

from(

selectstu.stu_name,s.score,sub.sub_name from SCORE s

innerjoin student stu

on(stu.s_id=s.stu_id)

innerjoin subject sub

on(sub.sub_id=s.sub_id)

wheresub.sub_id='5')t1

union

selectt2.stu_name as z,0as a,t2.score as b,0as c,0as d from(

selectstu.stu_name,s.score,sub.sub_name from SCORE s

innerjoin student stu

on(stu.s_id=s.stu_id)

innerjoin subject sub

on(sub.sub_id=s.sub_id)

wheresub.sub_id='6')t2

union

selectt3.stu_name as z,0as a,0as b,t3.score as c,0as d from(

selectstu.stu_name,s.score,sub.sub_name from SCORE s

innerjoin student stu

on(stu.s_id=s.stu_id)

innerjoin subject sub

on(sub.sub_id=s.sub_id)

wheresub.sub_id='7')t3

union

selectt4.stu_name as z,0as a,0as b,0as c,t4.score as d from(

selectstu.stu_name,s.score,sub.sub_name from SCORE s

innerjoin student stu

on(stu.s_id=s.stu_id)

innerjoin subject sub

on(sub.sub_id=s.sub_id)

wheresub.sub_id='8')t4

 

)t0

groupby t0.z

orderby总分desc

) t5

 groupby t5.姓名

 orderby总分排名 ;

 

 

select tt2.姓名as姓名,sum(tt2.html)as html,sum(tt2.jsp)as jsp,sum(tt2.sql)assql,sum(tt2.java)asjava,sum(tt2.html)+sum(tt2.jsp)+sum(tt2.sql)+sum(tt2.java)as总分,(sum(tt2.html)+sum(tt2.jsp)+sum(tt2.sql)+sum(tt2.java))/4as平均分,sum(rownum) as总分排名from(

selecttt1.stu_name as姓名,

 MAX(CASE  tt1.kc WHEN'HTML'THEN  tt1.sc ELSE0END) as html,

  MAX(CASE tt1.kc WHEN'JSP'THEN  tt1.sc ELSE0END) as jsp,

  MAX(CASE tt1.kc WHEN'SQL'THEN  tt1.sc ELSE0END) assql,

  MAX(CASE tt1.kc WHEN'JAVA'THEN  tt1.sc ELSE0END)asjava

from(

selectstu.stu_name,s.score as sc,sub.sub_name as kc from SCORE s

innerjoin student stu

on(stu.s_id=s.stu_id)

innerjoin subject sub

on(sub.sub_id=s.sub_id))tt1

groupby tt1.stu_name)tt2

groupby tt2.姓名

orderby总分desc

;