查询问题请教

[TsuLeon]

有课程、班级和成绩三张表
课程（编号、名称）
班级（编号、名称）
成绩（学生编号、班级编号、课程编号、成绩）

希望能统计各班级相应课程的平均分
实现如下形式的查询结果

班级 语文 数学 英语
-------------------
一班 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;

[Drate]

我也想知道，所以来MARK一下
顺便帮你UP!

[armu80830]

select c_classid,c_courseid,vag(c_score) group by c_courseid,c_classid

[armu80830]

打错了，用
select c_classid,c_courseid,avg(c_score) from t_score group by c_courseid,c_classid

[armu80830]

select c_classid,c_courseid,round(avg(c_score),1) from t_score group by c_courseid,c_classid order by c_classid,C_courseid

[benxie]

up

[armu80830]

这个是对的
select a.c_name,b.c_name,c.c_score
from t_class a,t_course b,
(select c_classid,c_courseid,round(avg(c_score),1) as c_score from t_score group by c_courseid,c_classid
order by c_classid,C_courseid) c
where a.c_id=c.c_classid and b.c_id=c.c_courseid;

[TsuLeon]

谢谢 armu80830(此情可待)

但是我希望实现的查询结果中
以课程为列
而且课程数由t_course动态确定