有三个表,学生表(T_Student)中有学生编号(StudentId)、学生名字(StudentName),课程表(T_Course)中有课程名字(CourseName)、课程编号(CourseId),成绩表(T_Grade)中有成绩(Grade)、学生编号(StudentId)、课程编号(CourseId),查询每门课程最高分的学生名字、课程名称、成绩?
我刚接触这一行,学习不是很理想,希望可以在这儿学到更多东西,望各位前辈多加指点,在这儿谢谢啦!
我刚接触这一行,学习不是很理想,希望可以在这儿学到更多东西,望各位前辈多加指点,在这儿谢谢啦!
http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html?65890
StudentName AS '学生名字',
CourseName AS '课程名称',
Grade AS '成绩'
FROM T_Student,T_Course,T_GradeWHERE T_Grade.StudentId = T_Student.StudentId and T_Course.CourseId = T_Grade.CourseId
from T_Student,T_Course,T_Grade
where T_Grade.StudentId = T_Student.StudentId and T_Course.CourseId =T_Grade.CourseId
select Student,CourseName,Grade from (select * from TGrade t1 where not exists(select 1 from T_Grade where CourseId=t1.CourseId and Grade>t1.Grade)) a
inner join T_Course b on a.CourseId=b.CourseId
inner join T_Student c on a.StudentId=c.StudentId
SELECT t2.StudentName,
t3.CourseName,
t1.Grade
FROM (
SELECT *
FROM T_Grade t
WHERE NOT EXISTS (
SELECT 1
FROM T_Grade
WHERE CourseId = t.CourseId
AND Grade > t.Grade
)
) t1
LEFT JOIN T_Student t2
ON t1.StudentId = t2.StudentId
LEFT JOIN T_Course t3
ON t1.CourseId = t2.CourseId
试试这个:
select a.StudentName,c.CourseName,b.Grade
from T_Student a inner join T_Grade b on a.StudentId=b.StudentId
inner join T_Course c on b.CourseId=c.CourseId
where not exists(select 1 from T_Grade where CourseId=b.CourseId and Grade>a.Grade)