帮忙看看这个SQL语句怎么写？！

[huangkw007]

学生表Student(student_id,student_name)
课程表Course(course_id,course_name,teacher_id)
教师表Teacher(teacher_id,teacher_name)
成绩表Grade(student_id,course_id,mark)
输出各科成绩最高和最低的相关记录.格式如下:
课程ID, 课程名称,最高分, 学生ID, 学生姓名, 教师ID, 教师姓名, 最低分, 学生ID, 学生姓名, 教师ID, 教师姓名
主要是最高分、最低分怎么单列出来？
这个SQL语句要怎么写？

[yhcs]

select course_id, max(mark) as Max_Score,min(mark) as Min_Score
from Grade group by course_id)

[huangkw007]

没有这么简单吧，是取每个课程的最高，不是单单去全部的最高。用MAX我知道，但要怎么用
还有一个就是：
按平均成绩从高到低顺序,输出所有学生的四门(数学，语文,英语, 政治)课程成绩.格式如下:
学生ID, 学生姓名, 数学, 语文,英语, 政治 , 有效课程数, 有效平均分
这个要怎么写？

[new_life]

-----------先建立两个用户自定义函数---------------------------------
--最高分函数
CREATE FUNCTION f_max (@course as int)
RETURNS @t table
(
student_id int,
student_name varchar(20),
course_id int,
mark int
) AS
BEGIN
insert into @t
SELECT Student.student_id, Student.student_name, Grade.course_id,
Grade.mark
FROM Grade INNER JOIN
Student ON Grade.student_id = Student.student_id
WHERE (Grade.mark =
(SELECT MAX(mark) AS mark_max
FROM Grade
WHERE course_id = @course))
return
END
--最低分函数
CREATE FUNCTION f_min
(@course as int)
RETURNS @t table
(
student_id int,
student_name varchar(20),
course_id int,
mark int
) AS
BEGIN
insert into @t
SELECT Student.student_id, Student.student_name, Grade.course_id,
Grade.mark
FROM Grade INNER JOIN
Student ON Grade.student_id = Student.student_id
WHERE (Grade.mark =
(SELECT MIN(mark) AS mark_max
FROM Grade
WHERE course_id = @course))
return
END
-------------再建立一个存储过程--------------------------------------------
CREATE PROCEDURE myProcedure
@course as int
AS
SELECT Course.course_id, Course.course_name, f_max.mark AS mark_max,
f_max.student_id AS mark_max_studentid,
f_max.student_name AS mark_max_studentname, teacher.teacher_id,
teacher.teacher_name, f_min.mark AS mark_min,
f_min.student_id AS mark_min_studentid,
f_min.student_name AS mark_min_studentname
FROM f_max(@course) f_max INNER JOIN
Course ON f_max.course_id = Course.course_id INNER JOIN
f_min(@course) f_min ON Course.course_id = f_min.course_id INNER JOIN
teacher ON Course.teacher_id = teacher.teacher_id
GO
---------------------调用这个存储过程就可以了-----------------------------------

[new_life]

我以试过，没有错误。

[caiyunxia]

2\select a.student_id as 学生ID,student_name as student_name,sum(case then course_name ='数学' then mark else 0 end) as 数学,sum(case then course_name ='语文' then mark else 0 end) as 语文,sum(case then course_name ='英语' then mark else 0 end) as 英语,sum(case then course_name ='政治' then mark else 0 end) as 政治,count(mark) as 有效课程数,sum(mark)/count(mark) as 有效平均分
from Student a inner join Grade b on a.student_id=b.student_id
inner join Course c on b.student_id=c.student_id
group by a.student_id ,student_name
order by sum(mark)/count(mark)

[caiyunxia]

select student_id,course_id,max(mark) maxmark from 成绩表 group by student_id,course_id