Sql Server：包含不属于聚合函数或Group by子句的列

时间：2022-01-13 20:39:30

I have a StudentGrades table that has these columns:

我有一个包含以下列的StudentGrades表:

 StudentID | CourseID | Grade

I need to find the top student for each course(along with the course id and the grade thye got for the course. For example if there are a total of three courses taught at the school the query should return:

我需要为每门课程找到最优秀的学生(以及课程编号和课程所获得的成绩。例如,如果学校总共有三门课程,那么查询应该返回:

1111  3  93
2334  4  90
4343  6  100

Here's what I have so far. The following query

这是我到目前为止所拥有的。以下查询

select CourseID, MAX(Grade) Grade 
from StudentGrades group by CourseID;

Produces: 

3   83
4   90
6   100

What is the best way to include the studentID column? I know that I can hold the above result in a temp table and join with the the original StudentGrades table where the CourseID and Score match the get the correct columns. Is there another way or better way to include StudentID?

包含studentID列的最佳方法是什么?我知道我可以将上述结果保存在临时表中并加入原始StudentGrades表,其中CourseID和Score匹配得到正确的列。是否有其他方式或更好的方式来包含StudentID?

Hope this makes sense.

希望这是有道理的。

Thanks!

1 个解决方案

#1

3  

SQL Server has a functionality for windowing functions.

SQL Server具有窗口函数的功能。

WITH gradeList
AS
(
    SELECT  StudentID,
            CourseID, 
            Grade,
            DENSE_RANK() OVER (PARTITION BY CourseID
                                ORDER BY Grade DESC) RN
    FROM    tableName
)
SELECT  StudentID,
        CourseID, 
        Grade
FROM    gradeList
WHERE   rn = 1

#1

3  

SQL Server has a functionality for windowing functions.

SQL Server具有窗口函数的功能。

WITH gradeList
AS
(
    SELECT  StudentID,
            CourseID, 
            Grade,
            DENSE_RANK() OVER (PARTITION BY CourseID
                                ORDER BY Grade DESC) RN
    FROM    tableName
)
SELECT  StudentID,
        CourseID, 
        Grade
FROM    gradeList
WHERE   rn = 1
标签：

相关文章