SQL Server中的内部联接 [英] inner join in SQL server

问题描述

我必须准备以下表格:

I have to following tables:

Student
	student_id
	fname
	lname
	year_id

Exam
	Exam_id
	Exam_name
	subject_id
	semester_id
        full mark
        pass mark

Semester
	semester_id
	semester_name
	year_id

Year
	year_id
	year_name

Subject
	subject_id
	subject_name
	year_id
	semester_id

Result
	Result_id
	student_id
	Exam_id
	score

Answers
      Answer ID
      Question ID
      correct_Answer
      The_Answer

Questions
       Question_ID
       Question Header
       Question_Group_Code
       Question_score
       Exam ID
       Subject ID


Question_Group
       Group_code
       Group_Description
       

I want to retrieve data reflect the most difficult questions in each exam? and The descending Sort students by the score of each student in each exam?

推荐答案

我的SQL知识不是很广泛，但是我认为SQl语句应该是:

My SQL knowledge is not extensive, but I think the SQl statement should be:

SELECT Student.fname, Student.lname, Subject.subject_name, Semester.semester_name, Year.year_name, MAX(Result.score) AS TopScore
FROM Result JOIN Student ON Result.student_id = Student.student_id
            JOIN Exam ON Result.Exam_id = Exam.Exam_id
            JOIN Subject ON Exam.subject_id = Subject.subject_id
            JOIN Semester ON Exam.semester_id = Semester.semester_id
            JOIN Year ON Semester.year_id = Year.year_id
GROUP BY Student.fname, Student.lname, Subject.subject_name, Semester.semester_name, Year.year_name

这篇关于SQL Server中的内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！