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

本文介绍了如何计算一级学生的总分，总分和得分最高的分数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

我的尝试：

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

SNAMEMARKSSUBJECT
SURESH98ENGLISH
SURESH97MATCHS
SURESH96SCIENCE
SUNIL95ENGLISH
SUNIL94MATHS
SUNIL99SCIENCE
RAMESH91ENGLISH
RAMESH92MATHS
RAMESH98SCIENCE

What I have tried:

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

推荐答案

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.

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