如何在SELECT语句中创建一个计算列，以确定某个成绩是否是奖励给学生的最新成绩

Question

我在MySQL 5.6数据库中有这个表。它收集了教师(usermodified(在每个评估项目(itemid(的每次尝试中授予学生的成绩(最终成绩(，评估项目可以是作业、测验、论坛讨论、课程总体成绩等。我只需要作业信息，所以我在source='mod/assign'上过滤此表以获取如下数据：

select t1.*
from mytable t1
where t1.source = 'mod/assign'

| id | itemid | source     | userid | finalgrade | usermodified |            feedback           | timemodified |
|:--:|:------:|------------|:------:|:----------:|:------------:|:-----------------------------:|:------------:|
| 1  | 4      | mod/assign | 3      |            | 3            |                               | 1596173158   |
| 15 | 4      | mod/assign | 3      |            | 3            |                               | 1596173203   |
| 16 | 4      | mod/assign | 3      | 2.00000    | 2            | <p>Needs more dragons<br></p> | 1596173324   |
| 18 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>Too many dragons<br></p>   | 1596173326   |
| 19 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>Needs more dragons<br></p> | 1596173339   |
| 20 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>4347jgfvgc<br></p>         | 1596173343   |
| 21 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>Apples and Carrots<br></p> | 1596173350   |
| 22 | 4      | mod/assign | 3      | 3.00000    | 2            | <p>khjgc<br></p>              | 1596173371   |
| 24 | 4      | mod/assign | 4      |            | 4            |                               | 1598234190   |
| 38 | 4      | mod/assign | 4      |            | 4            |                               | 1598234202   |
| 39 | 4      | mod/assign | 4      | 2.00000    | 2            |                               | 1598234334   |
| 41 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234335   |
| 42 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234341   |
| 43 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234356   |
| 44 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234361   |

我想在末尾添加一个名为Is Latest?的计算列，如果尝试是最新的尝试，它会说"是"，否则会说"否"。然而，我不知道该怎么做。我知道它必须使用id作为列来计算，因为教师可以在作业尝试中反悔分数(即使该尝试不是最新的尝试(，这会更新timemodified。该查询的输出应如下所示：

| id | itemid | source     | userid | finalgrade | usermodified |            feedback           | timemodified | is latest? |
|:--:|:------:|------------|:------:|:----------:|:------------:|:-----------------------------:|:------------:|------------|
| 1  | 4      | mod/assign | 3      |            | 3            |                               | 1596173158   | No         |
| 15 | 4      | mod/assign | 3      |            | 3            |                               | 1596173203   | No         |
| 16 | 4      | mod/assign | 3      | 2.00000    | 2            | <p>Needs more dragons<br></p> | 1596173324   | No         |
| 18 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>Too many dragons<br></p>   | 1596173326   | No         |
| 19 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>Needs more dragons<br></p> | 1596173339   | No         |
| 20 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>4347jgfvgc<br></p>         | 1596173343   | No         |
| 21 | 4      | mod/assign | 3      | 2.00000    | 3            | <p>Apples and Carrots<br></p> | 1596173350   | No         |
| 22 | 4      | mod/assign | 3      | 3.00000    | 2            | <p>khjgc<br></p>              | 1596173371   | Yes        |
| 24 | 4      | mod/assign | 4      |            | 4            |                               | 1598234190   | No         |
| 38 | 4      | mod/assign | 4      |            | 4            |                               | 1598234202   | No         |
| 39 | 4      | mod/assign | 4      | 2.00000    | 2            |                               | 1598234334   | No         |
| 41 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234335   | No         |
| 42 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234341   | No         |
| 43 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234356   | No         |
| 44 | 4      | mod/assign | 4      | 2.00000    | 4            |                               | 1598234361   | Yes        |

我在谷歌和StackOverflow上搜索过类似的情况，但都是空的。有人把这个问题标记为这个问题的关联，但我看不出有什么方法可以根据我的情况修改这个问题的答案。

我很感激你的帮助！

Answer 1

如果您使用的是MySQL 8+，那么ROW_NUMBER可以在这里使用：

SELECT *,
CASE WHEN ROW_NUMBER() OVER (PARTITION BY userid ORDER BY timemodified DESC) = 1
THEN 'Yes' ELSE 'No' END AS IsLatest
FROM yourTable
ORDER BY
timemodified;

对于MySQL的早期版本，我们可以加入一个子查询，为每个用户查找最新修改的时间：

SELECT t1.*,
CASE WHEN t1.timemodified = t2.maxtimemodified
THEN 'Yes' ELSE 'No' END AS IsLatest
FROM yourTable t1
LEFT JOIN
(
SELECT userid, MAX(timemodified) AS maxtimemodified
FROM yourTable
GROUP BY userid
) t2
ON t1.userid = t2.userid
ORDER BY
t1.timemodified;