从学生中选择测试的最后一次尝试

Question

我有一个表，里面有来自学生和考试的信息，每次学生参加考试时，都会在这个表中保存一条记录，所以我希望获得一次考试的学生的最后一次尝试。

| IdStudent | IdTest | Attempt|
-------------------------------
| 1         | 1      | 1      |
-------------------------------
| 2         | 1      | 1      |
-------------------------------
| 1         | 1      | 2      |
-------------------------------
| 2         | 2      | 1      |

假设我选择测试编号1，我想得到的记录是2和3，如果我选择测试2，我想得到的记录是4，依此类推。

我希望问题是清楚的。提前感谢你的帮助。

Answer 1

下面的SQL将为您提供所需的内容：

SELECT IdStudent, IdTest, MAX(Attempt)
FROM StudentTable
WHERE IdTest = @TestNumber
GROUP BY IdStudent, IdTest

Answer 2

@Jeff的答案会给你数字，但不会给你行。如果需要行中的其他数据，则必须使用子查询或CTE。如下所示：

WITH JeffAnswer AS
(
  SELECT IdStudent, IdTest, MAX(Attempt) AS MaxAttept
  FROM StudentTable
  WHERE IdTest = @TestNumber
  GROUP BY IdStudent, IdTest
)
SELECT * 
FROM StudentTable S
INNER JOIN JeffAnswer J ON
   S.IdStudent = J.IdStudent AND 
   S.IdTest = J.IdTest AND 
   S.Attempt = J.MaxAttempt

Answer 3

杰夫·霍恩比的一些替代方法：

Select ...
From Table As T1
Where Exists    (
                Select 1
                From Table As T2
                Where T2.IdStudent = T1.IdStudent
                    And T2.IdTest =T1.IdTest
                Having Max(T2.Attempt) = T1.Attempt
                )

如果您使用的是SQL Server 2005+：

With RankedTests As
    (
    Select IdStudent, IdTest, Attempt, ...
        , Row_Number() Over ( Partition By IdStudent, IdTest
                                 Order By Attempt Desc ) As Num
    From Table
    )
Select ...
From RankedTests
Where Num = 1