Oracle查询查找总数的最大值 [英] Oracle query to find the maximum of total

问题描述

我的学生表下面有这样的行

Hi,
I have the below student table with rows like this

student_id  subject_id     Mark
1               1                 60
1               2                 55
1               3                 70
2               1                 80      
2               2                 40
2               3                 50
3               1                 30      
3               2                 40
3               3                 60

我需要找到最大总分的student_id。有人可以帮忙。



谢谢



我的尝试：



我尝试使用max（sum）但没有得到正确的结果。

I need to find the student_id who has got maximum total marks.Could anyone please help.

Thanks

What I have tried:

I tried with using max(sum) but did not get the correct result.

推荐答案

类似于 -

Something like-

SELECT MAX(TotalMarks) AS TotalMarks
FROM (
    SELECT student_id,SUM(Mark) AS TotalMarks
    FROM Student
    GROUP BY student_id
) AS TBL

可能有更好的方法可以做到这一点，但它应该让你得到理想的结果。



希望，它有助于：）

There could be better way to do this but it should gie you the desired result.

Hope, it helps :)

这可以通过多种方式完成。一种非常简单的方法是使用查询根据标记对学生进行分组和排序，例如

This could be done several ways. One quite simple way is to use a query to group and sort the student's based on their marks, for example

SELECT a.Student_ID, SUM(a.Mark) AS Total
      FROM Student a
      GROUP BY a.Student_ID
      ORDER BY Total DESC

这会给你

This would give you

STUDENT_ID   TOTAL
----------   -----
1            185
2            170
3            130

现在选择第一个，您可以将结果用作内联视图并将行数限制为1。示例

Now to pick the first one, you can use the result as an inline view and limit the number of rows to 1. For example

SELECT *
FROM (SELECT a.Student_ID, SUM(a.Mark) AS Total
      FROM Student a
      GROUP BY a.Student_ID
      ORDER BY Total DESC
     ) b
WHERE RowNum <= 1;

这篇关于Oracle查询查找总数的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！