oracle 求班级平均分

 

select * from (

select
class 班级,
subject,
avg(grade) avg_grade
from student_score 
group by class,subject
) 
pivot (sum(avg_grade) for subject in ('语文', '数学','英语')) order by 班级 asc

 

求班级平均分
select 学科,平均分 别名 from 表
where class="121"
就是group by 班级,平均分

 1.121班的平均分

 

 2.行转列

 由于2的结果并没有班级这个字段，'121' 班级 凑字段（凑维度）

t121.*就是子查询。 查询t121表的所有字段

3.凑维度

4.联合查询union

 

 

 

 最终代码：

select '121' 班级,t121.* from (
select * from(
select subject,avg(grade) avg_grade from student_score
where class="121"
group by class,subject
)
pivot (sum(avg_grade) for subject in ('语文', '数学','英语'))
)t121
union
select '122' 班级,t122.* from (
select * from (
select subject,avg(grade) avg_grade from student_score
where class="122"
group by class,subject
)
pivot (sum(avg_grade) for subject in ('语文', '数学','英语'))
)t122