如何获得按关闭顺序排序的dicdict_userid

Question

我有一个名为info的表，它的数据是这样的

+----------+-------------------------+------+
| FKUserID |      CompletedTime      | Type |
+----------+-------------------------+------+
| 1        | 2021-03-10 12:56:00.423 | 5    |
+----------+-------------------------+------+
| 245      | 2021-03-10 12:46:46.977 | 5    |
+----------+-------------------------+------+
| 1        | 2021-03-10 12:44:53.683 | 5    |
+----------+-------------------------+------+
| 1        | 2021-03-10 12:40:54.733 | 5    |
+----------+-------------------------+------+
| 1        | 2021-03-10 12:35:26.307 | 5    |
+----------+-------------------------+------+
| 245      | 2021-03-10 11:11:33.887 | 5    |
+----------+-------------------------+------+
| 245      | 2021-03-10 10:18:11.403 | 5    |
+----------+-------------------------+------+

我需要得到不同的userID数据，也与他们的CompletedTime列的最大完成时间

期望输出是，

+----------+-------------------------+------+
| FKUserID |      CompletedTime      | Type |
+----------+-------------------------+------+
| 1        | 2021-03-10 12:56:00.423 | 5    |
+----------+-------------------------+------+
| 245      | 2021-03-10 12:46:46.977 | 5    |
+----------+-------------------------+------+

更新:对不起，我需要做这个Linq查询

Answer 1

对于这种类型的问题，我更喜欢使用ROW_NUMBER和TOP 1 WITH TIES:

SELECT TOP 1 WITH TIES *
FROM info
ORDER BY ROW_NUMBER() OVER (PARTITION BY FKUserID ORDER BY CompletedTime DESC);

Answer 2

你需要的是:https://www.w3schools.com/sql/sql_groupby.asp按用户Id分组并在CompletedTime上使用Max()，您将达到您的结果。

您的SQL将看起来像

SELECT `FKUserID`, MAX(`CompletedTime`) from `TableName` GROUP BY `FKUserID`;