我有一个表,它是一个关于用户何时登录的集合条目。

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 DISTINCT Username, Dates,value 
FROM TableName
WHERE  Dates IN (SELECT  MAX(Dates) FROM TableName GROUP BY Username)


Username    Dates       value
bob         2010-02-02  1.2       
brad        2010-01-02  1.1       
fred        2010-01-03  1.0       

其他回答

SELECT MAX(DATE) AS dates 
FROM assignment  
JOIN paper_submission_detail ON  assignment.PAPER_SUB_ID = 
     paper_submission_detail.PAPER_SUB_ID 

获取包含用户最大日期的整行信息:

select username, date, value
from tablename where (username, date) in (
    select username, max(date) as date
    from tablename
    group by username
)

这将为你编辑的问题提供正确的结果。

子查询确保只找到最近日期的行,而外部的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 distinct i.userId,i.statusCheck, l.userName from internetstatus 
as i inner join login as l on i.userID=l.userID 
where nowtime in((select max(nowtime) from InternetStatus group by userID));