MYSQL数据库查询返回最高分

Question

你好，我正在使用 mysql 为学校开发数据库。我被困在返回得分最高的学生的查询中。我尝试了不同的查询组合，但没有一个有效。一些返回了所有 100 分的学生(注意只有我的一个学生在测试数据中有 100 分)和其他返回测试数据中第一个 100 分的学生。我怀疑我的数据库有错误但我无法解决。

数据库:

CREATE TABLE Student
(
StudentID INT  auto_increment,
ClassID VARCHAR(10),
FirstName VARCHAR(25),
LastName VARCHAR(25),
DateOfBirth DATE,
Gender VARCHAR(10),
PRIMARY KEY (StudentID, ClassID)
);

create table Subjects
(
SubjectID INT,
SubjectName VARCHAR(25),
PRIMARY KEY (SubjectID)
);



create table Grade
(
StudentID INT,
SubjectID INT,
ClassID VARCHAR(10),
Grade DECIMAL(5,1),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (SubjectID) REFERENCES Subjects(SubjectID),
FOREIGN KEY (ClassID) REFERENCES Student(ClassID)
);



Test data:
INSERT INTO Student (StudentID, ClassID, FirstName, LastName, DateOfBirth, Gender)
VALUES ('', '01', 'John', 'Smith', '15/01/2000', 'Male'),
('', '01', 'Michael', 'Black', '15/03/2000', 'Male'),
('', '01', 'Dennis', 'White', '07/08/1999', 'Male'),
('', '01', 'Emy', 'Blue', '10/04/2000', 'Female'),
('', '01', 'Joe', 'Yellow', '09/05/2000', 'Male'),
('', '01', 'Aaren', 'Jackson', '09/009/1999', 'Male'),
('', '01', 'Marta', 'Harris', '30/01/2000', 'Female'),
('', '01', 'Laura', 'Lewis', '09/01/2000', 'Female'),
('', '01', 'Michael', 'Jackson', '01/01/2000', 'Male'),

('', '02', 'Piotr', 'Kowalski', '15/01/2000', 'Male'),
('', '02', 'Harris', 'Bialy', '15/03/2000', 'Male'),
('', '02', 'Porter', 'Czarny', '07/08/1999', 'Male'),
('', '02', 'Maciek', 'Blue', '10/04/2000', 'Female'),
('', '02', 'Mateusz', 'Yellow', '09/05/2000', 'Male'),
('', '02', 'Aaren', 'Jackson', '09/009/1999', 'Male'),
('', '02', 'Marta', 'Harris', '30/01/2000', 'Female'),
('', '02', 'Laura', 'Lewis', '09/01/2000', 'Female'),
('', '02', 'Chaytan', 'Jackson', '01/01/2000', 'Male');


INSERT INTO Subjects (SubjectID, SubjectName)
VALUES ('01', 'English'),
('02', 'Mathematics'),
('03', 'Science'),
('04', 'Geography'),
('05', 'IT'),
('06', 'History');

INSERT INTO Grade (StudentID, SubjectID, ClassID, Grade)
VALUES ('01', '1', '01', '60.5'),
('01', '2', '01', '70.0'),
('01', '3', '01', '40.0'),
('01', '4', '01', '33.5'),
('01', '5', '01', '90.0'),
('01', '6', '01', '77.5'),
('01', '7', '01', '89.0'),
('01', '8', '01', '74.0'),
('01', '9', '01', '66.5'),

('01', '10', '02', '30.5'),
('01', '11', '02', '50.0'),
('01', '12', '02', '30.0'),
('01', '13', '02', '73.5'),
('01', '14', '02', '70.0'),
('01', '15', '02', '57.5'),
('01', '16', '02', '69.0'),
('01', '17', '02', '34.0'),
('01', '18', '02', '76.5'),

('02', '1', '01', '65.5'),
('02', '2', '01', '73.0'),
('02', '3', '01', '41.0'),
('02', '4', '01', '39.5'),
('02', '5', '01', '96.0'),
('02', '6', '01', '70.5'),
('02', '7', '01', '80.0'),
('02', '8', '01', '74.0'),
('02', '9', '01', '64.5'),

('02', '10', '02', '55.5'),
('02', '11', '02', '43.0'),
('02', '12', '02', '61.0'),
('02', '13', '02', '49.5'),
('02', '14', '02', '76.0'),
('02', '15', '02', '80.5'),
('02', '16', '02', '99.0'),
('02', '17', '02', '100.0'),
('02', '18', '02', '55.5');

---

你能看出我哪里错了吗？你能告诉我适当的查询来检索得分最高的学生吗？谢谢

Answer 1

我想这就是你想要的，这会让你成为每个科目得分最高的学生，如果你想要一个特定的科目，你可以将 WHERE SubjectID=Subjects.SubjectID 更改为 WHERE SubjectID=particular_subject_id

SELECT Student.FirstName, Subjects.SubjectName, Grade.Grade, Subjects.SubjectID FROM 
Student INNER JOIN Grade ON Grade.StudentID=Student.StudentID 
INNER JOIN Subjects ON Grade.SubjectID=Subjects.SubjectID WHERE Grade.Grade=
(SELECT MAX(Grade) FROM Grade WHERE SubjectID=Subjects.SubjectID)

对于平均值:

 SELECT Subjects.SubjectName, AVG(Grade.Grade), Grade.ClassID, Subjects.SubjectID 
 FROM Grade INNER JOIN Subjects ON Grade.SubjectID=Subjects.SubjectID
 GROUP BY Grade.ClassID, Grade.SubjectID;

这是一个有效的 fiddle查看最高分和平均分的结果。