SQL：选择TOP N标记每用户（在用户的列表） [英] SQL: Select TOP N Marks Per User (In a List of Users)

问题描述

我有困难写一个存储过程，将查询学生名单及其关联标记。

I am having difficulty writing a Stored Procedure that will query a list of students with their associative marks.

检索学生名单 - 平凡
检索每个学生前5马克 - 平凡... SELECT TOP（5）*其中StudentID = X

Retrieving a List of Students - trivial Retrieving the top five marks per student - trivial...SELECT TOP (5) * WHERE StudentID = X

结合这两个，我有点糊涂了。

Combining these two, I am a bit confused.

我想存储过程返回两个表：

I would like the Stored Procedure to return two tables:

• 首先表由标准
列出学生

• 二表：等级列表（各5每名学生在第一个表）

第二表是当它是棘手的。我能得到所有每名学生在第一个表，但不知道我怎么能限制它向前五名标记。

Second table is when it is tricky. I can get all the marks per student in First Table but not sure how I can limit it to top 5.

推荐答案

如果你使用SQL 2005或更高，这​​应该工作。如果不是，还有其他略微凌乱的方式来做到这一点。

If you're using SQL 2005 or greater, this should work. If not, there are other slightly messier ways to do it.

WITH Student_Grades AS
(
    SELECT
    	S.student_id,
    	G.grade,
    	RANK() OVER (PARTITION BY S.student_id, ORDER BY G.exam_date DESC) AS grade_rank
    FROM
    	Students S
    LEFT OUTER JOIN Grades G ON
    	G.student_id = S.student_id
)
SELECT
    student_id,
    grade
FROM
    Student_Grades
WHERE
    grade_rank <= 5

这篇关于SQL：选择TOP N标记每用户（在用户的列表）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！