查询问题请教
有课程、班级和成绩三张表
课程(编号、名称)
班级(编号、名称)
成绩(学生编号、班级编号、课程编号、成绩)
希望能统计各班级相应课程的平均分
实现如下形式的查询结果
班级 语文 数学 英语
-------------------
一班 92.3 74.7 88.3
二班 88.0 92.0 91.0
三班 88.3 88.0 92.7
问题的关键是其中课程的数量和班级的数量不确定
函数或存储过程都可以
要是sql可以更好 :)
下面是相应的测试样例,请多指教!
drop table t_course;
drop table t_class;
drop table t_score;
create table t_course (
c_id number(2),
c_name varchar2(100)
);
create table t_class (
c_id number(2),
c_name varchar2(100)
);
create table t_score (
c_studentid number(2),
c_classid number(2),
c_courseid number(2),
c_score number(4, 1)
);
insert into t_course values(1, '语文');
insert into t_course values(2, '数学');
insert into t_course values(3, '英语');
insert into t_class values(1, '一班');
insert into t_class values(2, '二班');
insert into t_class values(3, '三班');
insert into t_score values(1, 1, 1, 87);
insert into t_score values(1, 1, 2, 56);
insert into t_score values(1, 1, 3, 78);
insert into t_score values(2, 1, 1, 97);
insert into t_score values(2, 1, 2, 86);
insert into t_score values(2, 1, 3, 95);
insert into t_score values(3, 1, 1, 93);
insert into t_score values(3, 1, 2, 82);
insert into t_score values(3, 1, 3, 92);
insert into t_score values(4, 2, 1, 93);
insert into t_score values(4, 2, 2, 95);
insert into t_score values(4, 2, 3, 90);
insert into t_score values(5, 2, 1, 83);
insert into t_score values(5, 2, 2, 89);
insert into t_score values(5, 2, 3, 92);
insert into t_score values(6, 3, 1, 86);
insert into t_score values(6, 3, 2, 94);
insert into t_score values(6, 3, 3, 89);
insert into t_score values(7, 3, 1, 91);
insert into t_score values(7, 3, 2, 71);
insert into t_score values(7, 3, 3, 89);
insert into t_score values(8, 3, 1, 88);
insert into t_score values(8, 3, 2, 99);
insert into t_score values(8, 3, 3, 100);
commit;