比较难的SQL语句？？？？？

[zhegong]

stud: stud_id, name, class_id, bithdate, age
class: class_id, name, create_date
course: course_id, name, teacher
score: course_id, stud_id, score


问题1：按班级统计每门课程的平均成绩、最高成绩、最低成绩。
包括： 班级名称、课程名称、平均成绩、最高成绩、最低成绩

问题2： 统计每门课程的最高成绩、最低成绩、最高成绩的学生姓名和所在班级、最低成绩的学生姓 名和所在班级

[fcuandy]

语句我不想写了,这几天这种写的很多了.请参考这个贴子.

http://community.csdn.net/Expert/topic/4874/4874196.xml?temp=.4765131

[fcuandy]

区别就在,你的这个问题比起我发的地址里的那个问题做起来要多连个表.仅此而已.

[liuyinbo0109]

select b.name,c.Course_ID,最高分= max(score),最低分= min(score),平均分=avg(score)
from stud a,
class b,
course c,
score d
where a.class_id=b.class_id and c.course_id=d.course_id and a.stud_id=d.stud_id
group by b.name,c.course_ID
order by b.name

[十一月猪]

Problem1 :
select b.name ,
c.name ,
avg(c.score) ,
max(c.score) ,
min(c.score)
from stud a ,class b , course c , score d
where a.class_id = b.class_id
and a.stud_id = d.stud_id
and d.course_id = c.course_id
group by b.name , c.name

Problem2 :

select * ,
'min' as flag
from
( select c.name as kc,
d.score as fs,
a.name as xsxm,
b.name as bz
from stud a ,class b , course c , score d
where a.class_id = b.class_id
and a.stud_id = d.stud_id
and d.course_id = c.course_id
) d
where not exists(
select 1 from
( select c.name as kc,
d.score as fs,
a.name as xsxm,
b.name as bz
from stud a ,class b , course c , score d
where a.class_id = b.class_id
and a.stud_id = d.stud_id
and d.course_id = c.course_id
) e
where d.kc = e.kc and d.fs > e.fs
)

union all

select * ,
'max' as flag
from
( select c.name as kc,
d.score as fs,
a.name as xsxm,
b.name as bz
from stud a ,class b , course c , score d
where a.class_id = b.class_id
and a.stud_id = d.stud_id
and d.course_id = c.course_id
) d
where not exists(
select 1 from
( select c.name as kc,
d.score as fs,
a.name as xsxm,
b.name as bz
from stud a ,class b , course c , score d
where a.class_id = b.class_id
and a.stud_id = d.stud_id
and d.course_id = c.course_id
) e
where d.kc = e.kc and d.fs < e.fs
)

[橡胶轮胎行业数字化高总]

正解了！