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

本文介绍了Sql Server：包含不属于聚合函数或Group by子句的列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个StudentGrades表，它包含以下列：

 > SQL Server 具有窗口函数的功能。 b 
 $ b 
  WITH gradeList 
 AS 
（
 SELECT StudentID，
 CourseID，
等级， 
 DENSE_RANK（）OVER（划分为CourseID 
 ORDER BY Grade DESC）RN 
 FROM tableName 
）
 SELECT StudentID，
 CourseID，
 Grade 
 FROM gradeList 
 WHERE rn = 1 
  
 
 
 
 
   
 
   
 
   SQLFiddle Demo（带有重复的学生与最高成绩相同的分数） li> 
 
 
I have a StudentGrades table that has these columns:  
 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?
Hope this makes sense.
Thanks!
 解决方案 
SQL Server has a functionality for windowing functions.
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
SQLFiddle Demo
SQLFiddle Demo
SQLFiddle Demo (with duplicate student having the same score with the highest grade)
                        这篇关于Sql Server：包含不属于聚合函数或Group by子句的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！