MYSQL数据库查询返回最高分 [英] MYSQL Database query to return highest score

问题描述

您使用mysql在学校数据库工作。我停留在查询返回学生的最高分。我尝试了不同的查询组合，没有一个工作。一些返回所有100级的学生（注意，我的一个学生在测试数据中只有一个100级）和其他返回第一个学生在测试数据与100级。我怀疑我的数据库中有一个错误，但我不能解决它。 / p>

DB：

  CREATE TABLE Student 
 $ b StudentID INT auto_increment，
 ClassID VARCHAR（10），
 FirstName VARCHAR（25），
 LastName VARCHAR（25），
 DateOfBirth DATE，
性别VARCHAR 10），
 PRIMARY KEY（StudentID，ClassID）
）; 
 
创建表主题
（
 SubjectID INT，
 SubjectName VARCHAR（25），
 PRIMARY KEY（SubjectID）
）; 
 
 
 
创建表成绩
（
 StudentID INT，
 SubjectID INT，
 ClassID VARCHAR（10），
成绩DECIMAL（5,1），
 FOREIGN KEY（StudentID）参考文献学生（StudentID），
 FOREIGN KEY（SubjectID）参考文献主题（SubjectID），
 FOREIGN KEY学生（ClassID）
）; 
 
 
 
测试数据：
 INSERT INTO学生（StudentID，ClassID，FirstName，LastName，DateOfBirth，Gender）
 VALUES（''，'01 ''，'John'，'Smith'，'15 / 01/2000'，'Male'），
 ，'Male'），
（''，'01'，'Dennis'，'White'，'07 / 08/1999' ，'A'，'Blue'，'10 / 04/2000'，'Female'），
（''，'01'，'Joe'，'Yellow'，'09 / 05/2000' 'male'），
（''，'01'，'Aaren'，'Jackson'，'09 / 009/1999' 'Marta'，'Harris'，'30 / 01/2000'，'Female'），
（''，'01'，'Laura'，'Lewis'，'09 / 01/2000'女性'），
（''，'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' '，'Maciek'，'Blue'，'10 / 04/2000'，'Female'），
（''，'02'，'Mateusz'，'Yellow'，'09 / 05 / ，'Male'），
（''，'02'，'Aaren'，'Jackson'，'09 / 009/1999' ，'Marta'，'Harris'，'30 / 01/2000'，'Female'），
（''，'02'，'Laura'，'Lewis'，'09 / 01/2000' 'female'），
（''，'02'，'Chaytan'，'Jackson'，'01 / 01/2000'，'Male'）; 
 
 
 INSERT INTO主题（SubjectID，SubjectName）
 VALUES（'01'，'English'），
（'02'，'Mathematics'），
（'03'，'Science'），
（'04'，'Geography'），
（'05'，'IT'），
 '历史'）; 
 
 INSERT INTO成绩（StudentID，SubjectID，ClassID，Grade）
 VALUES（'01'，'1'，'01'，'60 .5'），
 ''，'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'），
 ''，'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'），
 '4'，'01'，'39.5'），
（'02'，'5'，'01'，'96 .0'），
（'02'，'6' '，'70 .5'），
（'02'，'7'，'01'，'80 .0'），
（'02'，'8'，'01'，'74 .0'） ，
（'02'，'9'，'01'，'64 .5'），
 
（'02'，'10'，'02'，'55.5' b $ b（'02'，'11'，'02'，'43 .0'），
（'02'，'12'，'02'，'61.0'），
 ''，'13'，'02'，'49.5'），
（'02'，'14'，'02'，'76 .0'），
（'02'，'15' '02'，'80.5'），
（'02'，'16'，'02'，'99.0'），
（'02'，'17'，'02' '），
（'02'，'18'，'02'，'55 .5'）; 
  

---

你能看到我错了吗？你能告诉我适当的查询来检索学生的最高分。感谢

解决方案

我认为这是你想要的，这会让你在每个主题得分最高的学生，如果你想要特定主题，可以将 WHERE SubjectID = Subjects.SubjectID 更改为 WHERE SubjectID = specific_subject_id

  SELECT Student.FirstName，Subjects.SubjectName，Grade.Grade，Subjects.SubjectID FROM 
学生INNER JOIN年级ON Grade.StudentID =学生.StudentID 
 INNER JOIN主题ON Grade.SubjectID =主题.SubjectIDWHERE成绩.Grade = 
（SELECT MAX（成绩）FROM成绩WHERE SubjectID = Subjects.SubjectID）
  

对于平均值：

  SubjectName，AVG（Grade.Grade），Grade.ClassID，Subjects.SubjectID 
从成绩INNER JOIN主题ON成绩.SubjectID =主题.SubjectID 
 GROUP BY Grade.ClassID，Grade.SubjectID; 
  

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

Hello im working on database for school using mysql. I got stuck on the query to return student with the highest score. I tried different combinations of queries and none of them worked. Some returned all students with grade 100 (note only one of my students have grade 100 in the test data) and other returned First student in test data with grade 100. I suspect there is an error in my database but i cannot solve it.

DB:

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');

---

Can you see where have i gone wrong? Could you also tell me appropriate query to retrieve student with highest score. Thanks

解决方案

I think this is what you want, this will get you the student with the highest score on each subject, if you want a particular subject, you can change WHERE SubjectID=Subjects.SubjectID to 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)

For average:

 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;

Here's a working fiddle to see results of highest score and average.

这篇关于MYSQL数据库查询返回最高分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！