如何查询 sql 以获取每个用户的最新记录日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2411559/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how do I query sql for a latest record date for each user
提问by fishhead
I have a table that is a collection entries as to when a user was logged on.
我有一个表，它是关于用户何时登录的集合条目。
username, date, value
--------------------------
brad, 1/2/2010, 1.1
fred, 1/3/2010, 1.0
bob, 8/4/2009, 1.5
brad, 2/2/2010, 1.2
fred, 12/2/2009, 1.3
etc..
How do I create a query that would give me the latest date for each user?
如何创建一个查询，为我提供每个用户的最新日期？
Update:I forgot that I needed to have a value that goes along with the latest date.
更新：我忘了我需要一个与最新日期一致的值。
回答by RedFilter
select t.username, t.date, t.value
from MyTable t
inner join (
select username, max(date) as MaxDate
from MyTable
group by username
) tm on t.username = tm.username and t.date = tm.MaxDate
回答by dotjoe
Using window functions (works in Oracle, Postgres 8.4, SQL Server 2005, DB2, Sybase, Firebird 3.0, MariaDB 10.3)
使用窗口函数（适用于 Oracle、Postgres 8.4、SQL Server 2005、DB2、Sybase、Firebird 3.0、MariaDB 10.3）
select * from (
select
username,
date,
value,
row_number() over(partition by username order by date desc) as rn
from
yourtable
) t
where t.rn = 1
回答by sujeet
I see most of the developers use an inline query without considering its impact on huge data.
我看到大多数开发人员使用内联查询时没有考虑它对大量数据的影响。
Simply, you can achieve this by:
简单地说，您可以通过以下方式实现：
SELECT a.username, a.date, a.value
FROM myTable a
LEFT OUTER JOIN myTable b
ON a.username = b.username
AND a.date < b.date
WHERE b.username IS NULL
ORDER BY a.date desc;
回答by Alison R.
To get the whole row containing the max date for the user:
要获取包含用户最大日期的整行：
select username, date, value
from tablename where (username, date) in (
select username, max(date) as date
from tablename
group by username
)
回答by Manix
SELECT *
FROM MyTable T1
WHERE date = (
SELECT max(date)
FROM MyTable T2
WHERE T1.username=T2.username
)
回答by Fabian Pijcke
From my experience the fastest way is to take each row for which there is no newer row in the table.
根据我的经验，最快的方法是获取表中没有更新行的每一行。
Another advantage is that the syntax used is very simple, and that the meaning of the query is rather easy to grasp (take all rows such that no newer row exists for the username being considered).
另一个优点是使用的语法非常简单，并且查询的含义很容易掌握（获取所有行，以便所考虑的用户名不存在较新的行）。
NOT EXISTS
不存在
SELECT username, value
FROM t
WHERE NOT EXISTS (
SELECT *
FROM t AS witness
WHERE witness.username = t.username AND witness.date > t.date
);
ROW_NUMBER
ROW_NUMBER
SELECT username, value
FROM (
SELECT username, value, row_number() OVER (PARTITION BY username ORDER BY date DESC) AS rn
FROM t
) t2
WHERE rn = 1
INNER JOIN
内部联接
SELECT t.username, t.value
FROM t
INNER JOIN (
SELECT username, MAX(date) AS date
FROM t
GROUP BY username
) tm ON t.username = tm.username AND t.date = tm.date;
LEFT OUTER JOIN
左外连接
SELECT username, value
FROM t
LEFT OUTER JOIN t AS w ON t.username = w.username AND t.date < w.date
WHERE w.username IS NULL
回答by Peter Lang
This one should give you the correct result for your edited question.
这个应该为您编辑的问题提供正确的结果。
The sub-query makes sure to find only rows of the latest date, and the outer GROUP BYwill take care of ties. When there are two entries for the same date for the same user, it will return the one with the highest value.
子查询确保只找到最新日期的行，而外部GROUP BY将处理关系。当同一用户的同一日期有两个条目时，它将返回最高的一个value。
SELECT t.username, t.date, MAX( t.value ) value
FROM your_table t
JOIN (
SELECT username, MAX( date ) date
FROM your_table
GROUP BY username
) x ON ( x.username = t.username AND x.date = t.date )
GROUP BY t.username, t.date
回答by imba22
You could also use analytical Rank Function
您还可以使用分析排名函数
with temp as
(
select username, date, RANK() over (partition by username order by date desc) as rnk from t
)
select username, rnk from t where rnk = 1
回答by ashish bindra
SELECT MAX(DATE) AS dates
FROM assignment
JOIN paper_submission_detail ON assignment.PAPER_SUB_ID =
paper_submission_detail.PAPER_SUB_ID
回答by Narmadha
SELECT *
FROM ReportStatus c
inner join ( SELECT
MAX(Date) AS MaxDate
FROM ReportStatus ) m
on c.date = m.maxdate
