如何计算一级学生的总分，总分和得分最高的分数 [英] How to calculate 1st rank student with his total gross marks , the subject and marks which has maximum score

问题描述

SNAME MARKS SUBJECT

SURESH 98 ENGLISH

SURESH 97 MATCHS

SURESH 96 SCIENCE

SUNIL 95 ENGLISH

SUNIL 94 MATHS

SUNIL 99 SCIENCE

RAMESH 91 ENGLISH

RAMESH 92 MATHS

RAMESH 98 SCIENCE



我的尝试：



如何计算一级学生的总分，总分和得分最高的分数。

SNAME MARKS SUBJECT
SURESH 98 ENGLISH
SURESH 97 MATCHS
SURESH 96 SCIENCE
SUNIL 95 ENGLISH
SUNIL 94 MATHS
SUNIL 99 SCIENCE
RAMESH 91 ENGLISH
RAMESH 92 MATHS
RAMESH 98 SCIENCE

What I have tried:

how to calculate 1st rank student with his total gross marks ,the subject and marks which has maximum score.

推荐答案

分阶段进行：首先获得学生总分和最高分个人得分，并获得最高得分者：

Do it in stages: first get the students total score and highest personal score, and get the highest scorer:

SELECT TOP 1 SNAME, SUM(Marks) As Total, MAX(Marks) As Highest
FROM MyTable
GROUP BY SName
ORDER By Total DESC

返回一名学生：

That returns one student:

SName  Total   Highest
SURESH	 291        98

然后用原始表格加入以获得主题：

Then JOIN that with the original table to get the subject as well:

SELECT s.SName, s.Total, a.Subject, s.Highest
FROM MyTable a
JOIN (SELECT TOP 1 SNAME, SUM(Marks) As Total, MAX(Marks) As Highest
        FROM MyTable
        GROUP BY SName
        ORDER By Total DESC) s 
ON s.SName = a.SName AND s.Highest = a.Marks

结果：一行，您需要的信息。

Result: one row, the info you need.

SName     Total   Subject  Highest
SURESH	    291   English       98

但是......这是一个非常糟糕的数据库设计。您重复存储相同的信息，并且应该使用三个表：一个用于学生，一个用于主题，一个用于使用外键返回其他结果的结果。

But...that is a very poor database design. You are storing the same info repeatedly, and should be using three tables: one for students, one for subjects, and one for results which uses foreign keys back to the others.

这篇关于如何计算一级学生的总分，总分和得分最高的分数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！