获取列具有最大值的行

问题描述

表格:

UserId, Value, Date.

我想获取每个用户 ID 的用户 ID，最大值(日期)的值.即，具有最新日期的每个 UserId 的值.有没有办法在 SQL 中简单地做到这一点?(最好是 Oracle)

I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date. Is there a way to do this simply in SQL? (Preferably Oracle)

更新: 对任何歧义表示歉意:我需要获取所有用户 ID.但是对于每个 UserId，只有该用户具有最新日期的那一行.

Update: Apologies for any ambiguity: I need to get ALL the UserIds. But for each UserId, only that row where that user has the latest date.

推荐答案

这将检索 my_date 列值等于该用户 ID 的 my_date 最大值的所有行.这可能会为 userid 检索多行，其中最大日期在多行上.

This will retrieve all rows for which the my_date column value is equal to the maximum value of my_date for that userid. This may retrieve multiple rows for the userid where the maximum date is on multiple rows.

select userid,
       my_date,
       ...
from
(
select userid,
       my_date,
       ...
       max(my_date) over (partition by userid) max_my_date
from   users
)
where my_date = max_my_date

解析函数摇滚"

关于第一条评论......

With regard to the first comment ...

使用分析查询和自联接违背了分析查询的目的"

"using analytic queries and a self-join defeats the purpose of analytic queries"

此代码中没有自联接.相反，在包含分析函数的内联视图的结果上放置了一个谓词——这是完全不同的事情，而且完全是标准做法.

There is no self-join in this code. There is instead a predicate placed on the result of the inline view that contains the analytic function -- a very different matter, and completely standard practice.

Oracle 中的默认窗口是从分区的第一行到当前行"

"The default window in Oracle is from the first row in the partition to the current one"

windowing 子句只适用于 order by 子句.没有 order by 子句，默认不应用窗口子句，也不能显式指定.

The windowing clause is only applicable in the presence of the order by clause. With no order by clause, no windowing clause is applied by default and none can be explicitly specified.

代码有效.

这篇关于获取列具有最大值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！