行列转换——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.zas姓名,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_nameas z,t1.score as a,0as b,0as c,0as d

from(

selectstu.stu_name,s.score,sub.sub_namefrom 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_nameas z,0as a,t2.scoreas b,0as c,0as d from(

selectstu.stu_name,s.score,sub.sub_namefrom 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_nameas z,0as a,0as b,t3.score as c,0as dfrom(

selectstu.stu_name,s.score,sub.sub_namefrom 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_nameas z,0as a,0as b,0as c,t4.scoreas d from(

selectstu.stu_name,s.score,sub.sub_namefrom 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_nameas姓名,

 MAX(CASE  tt1.kcWHEN'HTML'THEN  tt1.scELSE0END)as html,

  MAX(CASE tt1.kcWHEN'JSP'THEN  tt1.scELSE0END)as jsp,

  MAX(CASE tt1.kcWHEN'SQL'THEN  tt1.scELSE0END)assql,

  MAX(CASE tt1.kcWHEN'JAVA'THEN  tt1.scELSE0END)asjava

from(

selectstu.stu_name,s.scoreas sc,sub.sub_nameas 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

;