(select a.course_id,a.course_name,b.,c.student_id,c.student_name.d.teacher_id,d.teacher_name from Course a,Grade b,Student c,Teacher d where a.course_id =b.course_id and a.teacher_id =d.teacher_id and b. student_id = c.student_id) x得到子表,然后再在这个表基础上查询。难倒不难就是烦
grade g
inner join course c
on g.course_id=c.course_id
inner join student s
on g.student_id=s.student_id
inner join teacher t
on c.teacher_id=t.teacher_id
where g.=(select max() from g) or g.=(select min() from g)
group by g.course_id
--最高分函数
CREATE FUNCTION f_max (@course as int)
RETURNS @t table
(
student_id int,
student_name varchar(20),
course_id int,
int
) AS
BEGIN
insert into @t
SELECT Student.student_id, Student.student_name, Grade.course_id,
Grade.
FROM Grade INNER JOIN
Student ON Grade.student_id = Student.student_id
WHERE (Grade. =
(SELECT MAX() AS _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,
int
) AS
BEGIN
insert into @t
SELECT Student.student_id, Student.student_name, Grade.course_id,
Grade.
FROM Grade INNER JOIN
Student ON Grade.student_id = Student.student_id
WHERE (Grade. =
(SELECT MIN() AS _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. AS _max,
f_max.student_id AS _max_studentid,
f_max.student_name AS _max_studentname, teacher.teacher_id,
teacher.teacher_name, f_min. AS _min,
f_min.student_id AS _min_studentid,
f_min.student_name AS _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
---------------------调用这个存储过程就可以了-----------------------------------
from Course a inner join (select student_id,course_id,max() max from 成绩表 group by student_id,course_id) b on a.course_id =b .course_id inner join Course c on c.course_id=b.course_id inner join Teacher d on d.teacher_id=c.teacher_id
select a.student_id as 学生ID,student_name as student_name,sum(case then course_name ='数学' then else 0 end) as 数学,sum(case then course_name ='语文' then else 0 end) as 语文,sum(case then course_name ='英语' then else 0 end) as 英语,sum(case then course_name ='政治' then else 0 end) as 政治,count() as 有效课程数,sum()/count() 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
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()/count()
服务器: 消息 207,级别 16,状态 3,行 1
列名 'c' 无效。
服务器: 消息 207,级别 16,状态 1,行 1
列名 'student_name' 无效。
服务器: 消息 209,级别 16,状态 1,行 1
列名 'teacher_id' 不明确。
第2个问题还是有错
服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'then' 附近有语法错误。
1。输出各科成绩最高和最低的相关记录.格式如下:
课程ID, 课程名称,最高分, 学生ID, 学生姓名, 教师ID, 教师姓名, 最低分, 学生ID, 学生姓名, 教师ID, 教师姓名
*/
select course.course_id, course.course_name,
max_grade, maxgrade.student_id, maxgrade.student_name, maxgrade.teacher_id, maxgrade.teacher_name,
min_grade, mingrade.student_id, mingrade.student_name, mingrade.teacher_id, mingrade.teacher_name
from
course,(select t.course_id, max_grade, student.student_id, student_name, teacher.teacher_id, teacher_name
from
grade, student, teacher, course,
(select course_id, max() as max_grade from grade group by course_id) t
where
grade.course_id = t.course_id
and grade.student_id = student.student_id
and course.course_id = grade.course_id
and teacher.teacher_id = course.teacher_id
and grade. = max_grade) maxgrade, (select t.course_id, min_grade, student.student_id, student_name, teacher.teacher_id, teacher_name
from
grade, student, teacher, course,
(select course_id, min() as min_grade from grade group by course_id) t
where
grade.course_id = t.course_id
and grade.student_id = student.student_id
and course.course_id = grade.course_id
and teacher.teacher_id = course.teacher_id
and grade. = min_grade) mingradewhere
course.course_id = maxgrade.course_id
and course.course_id = mingrade.course_id
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()/count() case when then else end
from Course a inner join (select student_id,course_id,max() max from 成绩表 group by student_id,course_id) b on a.course_id =b .course_id inner join Course c on c.course_id=b.course_id inner join Teacher d on d.teacher_id=c.teacher_id
from grade a
join grade b on a.course_id = b.course_id
where a. = (select max()
from grade
where course_id = a.course_id)
and
b. = (select min()
from grade
where course_id = b.course_id)
SELECT student_id
,(SELECT FROM grade WHERE student_id=grade.student_id AND course_id='K1') AS 数学
,(SELECT FROM grade WHERE student_id=grade.student_id AND course_id='K2') AS 语文
,(SELECT FROM grade WHERE student_id=grade.student_id AND course_id='K3') AS 英语
,(SELECT FROM grade WHERE student_id=grade.student_id AND course_id='K4') AS 政治
,COUNT(*) AS 有效课程数, AVG() AS 平均成绩
FROM grade
GROUP BY student_id
ORDER BY 平均成绩