Sql Server：包含不属于聚合函数或Group by子句的列 [英] Sql Server: include column that is not part of an aggregate function or Group by clause

问题描述

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

  StudentID | CourseID |年级
  

我需要找到每门课程的最佳学生（以及课程编号和成绩照片例如，如果在学校总共教授三门课程，则查询应返回：

  1111 3 93 
 2334 4 90 
 4343 6 100 
  

这是我到目前为止。以下查询

 根据CourseID从StudentGrades组中选择CourseID，MAX（成绩）成绩
; 
  

产生：

  3 83 
 4 90 
 6 100 
  

什么是最佳方式包括studentID列？我知道我可以将上面的结果保存在一个临时表中，并加入原始StudentGrades表格，其中CourseID和Score匹配得到正确的列。是否有其他方式或更好的方式来包含StudentID？ / p>

希望这是有道理的。

谢谢！

p>

解决方案> 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演示


• SQLFiddle Demo


• 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子句的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！