我有一个表，它是一个关于用户何时登录的集合条目。
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..
我如何创建一个查询，将给我每个用户的最新日期?
更新:我忘记了我需要有一个值与最近的日期。
当前回答
SELECT t1.username, t1.date, value
FROM MyTable as t1
INNER JOIN (SELECT username, MAX(date)
FROM MyTable
GROUP BY username) as t2 ON t2.username = t1.username AND t2.date = t1.date
其他回答
获取包含用户最大日期的整行信息:
select username, date, value
from tablename where (username, date) in (
select username, max(date) as date
from tablename
group by username
)
SELECT *
FROM ReportStatus c
inner join ( SELECT
MAX(Date) AS MaxDate
FROM ReportStatus ) m
on c.date = m.maxdate
这将为你编辑的问题提供正确的结果。
子查询确保只找到最近日期的行，而外部的GROUP BY将负责联系。当同一用户的同一日期有两个条目时，它将返回值最高的那个。
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
我看到大多数开发人员在使用内联查询时没有考虑它对大数据的影响。
简单地说，你可以通过:
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;
SELECT t1.username, t1.date, value
FROM MyTable as t1
INNER JOIN (SELECT username, MAX(date)
FROM MyTable
GROUP BY username) as t2 ON t2.username = t1.username AND t2.date = t1.date
