只选择日期的最后一个值? [英] Select only last value of date?

问题描述

我有一个 SELECT，它返回与这个结果类似的结果(我已经简化了它，因为原来的 SELECT 相当复杂):

I have a SELECT, that returns me something similar to this result (I've simplified it, because the original SELECT is rather complex):

| UserFK    | aDate             | aValue    |
---------------------------------------------
| 1000      | 03.12.2012 10:00  | 123       |
| 1000      | 03.12.2012 11:00  | 456       | <--
| 1001      | 03.12.2012 09:00  | 111       | <--
| 1002      | 03.12.2012 09:00  | 222       |
| 1002      | 03.12.2012 10:00  | 333       | <--
| 1003      | 03.12.2012 09:00  | 123       | 
| 1003      | 02.12.2012 09:00  | 123       |
| 1003      | 03.12.2012 10:00  | 455       | <--
| 1004      | 03.12.2012 11:00  | 123       | <--

现在我不想要任何重复的 UserFK，我只想要最后一个(按 aDate)UserFK 和它的值.我已经标记了我真正想要的.

Now I don't want any duplicate UserFK, I only want the last (by aDate) UserFK and it's value. I've marked the ones that I actually want.

有简单的方法吗?

推荐答案

您可以将 CTE 与 ROW_NUMBER 像这样:

You can use a CTE with a ROW_NUMBER like this:

WITH CTE AS
(
   SELECT UserFK, aDate, aValue,
     RN = ROW_NUMBER() OVER (PARTITION BY UserFK ORDER BY aDate DESC)
   FROM dbo.TableName
)
SELECT UserFK, aDate, aValue
FROM CTE
WHERE RN = 1

这篇关于只选择日期的最后一个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！