Oracle-习题
习题：增加1个字段，各门功课是否超过平均分
-- 回顾：行转列
-- 1.利用decode（不只是用于行转列）
-- 2.利用oracle 11g以后提供pivot
select
ooo.*,
1 struts_avg,
2 srping_avg,
3 hibernate_avg,
4 oracle_avg
from (
select * from (
(select user_name,ke,score from t_user_score)
)
pivot(
sum(score) -- 统计值
for ke in ('struts','spring','hibernate','oracle') -- 划分多少列
)
) ooo
------------
-- struts的平均分
select * from t_user_score where ke='struts'
select avg(score) from t_user_score where ke='struts'
-------------------
select id ,
(select 1 from dual) pp
from t_user_score
--------------------------
select
ooo.*,
(select avg(score) from t_user_score where ke='struts') struts_avg,
(select avg(score) from t_user_score where ke='spring') srping_avg,
(select avg(score) from t_user_score where ke='hibernate') hibernate_avg,
(select avg(score) from t_user_score where ke='oracle') oracle_avg
from (
select * from (
(select user_name,ke,score from t_user_score)
)
pivot(
sum(score) -- 统计值
for ke in ('struts' as struts,'spring' as spring,'hibernate' as hibernate,'oracle' as oracle) -- 划分多少列
)
) ooo
--------------
select
ooo.*,
(select avg(score)-struts from t_user_score where ke='struts') low_struts_avg,
(select avg(score)-spring from t_user_score where ke='spring') low_srping_avg,
(select avg(score)-hibernate from t_user_score where ke='hibernate') low_hibernate_avg,
(select avg(score)-oracle from t_user_score where ke='oracle') low_oracle_avg
from (
select * from (
(select user_name,ke,score from t_user_score)
)
pivot(
sum(score) -- 统计值
for ke in ('struts' as struts,'spring' as spring,'hibernate' as hibernate,'oracle' as oracle) -- 划分多少列
)
) ooo
-------------------------------
select
ooo.*,
sign((select avg(score)-struts from t_user_score where ke='struts')) low_struts_avg,
sign((select avg(score)-spring from t_user_score where ke='spring')) low_srping_avg,
sign((select avg(score)-hibernate from t_user_score where ke='hibernate')) low_hibernate_avg,
sign((select avg(score)-oracle from t_user_score where ke='oracle')) low_oracle_avg
from (
select * from (
(select user_name,ke,score from t_user_score)
)
pivot(
sum(score) -- 统计值
for ke in ('struts' as struts,'spring' as spring,'hibernate' as hibernate,'oracle' as oracle) -- 划分多少列
)
) ooo
----------------------
select
ooo.*,
decode(sign((select avg(score)-struts from t_user_score where ke='struts')),1,'是的，低于平均分',-1,'高于平均分','跟平均分持平') low_struts_avg,
decode(sign((select avg(score)-spring from t_user_score where ke='spring')),1,'是的，低于平均分',-1,'高于平均分','跟平均分持平') low_srping_avg,
decode(sign((select avg(score)-hibernate from t_user_score where ke='hibernate')),1,'是的，低于平均分',-1,'高于平均分','跟平均分持平') low_hibernate_avg,
decode(sign((select avg(score)-oracle from t_user_score where ke='oracle')),1,'是的，低于平均分',-1,'高于平均分','跟平均分持平') low_oracle_avg
from (
select * from (
(select user_name,ke,score from t_user_score)
)
pivot(
sum(score) -- 统计值
for ke in ('struts' as struts,'spring' as spring,'hibernate' as hibernate,'oracle' as oracle) -- 划分多少列
)
) ooo
