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

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..

我如何创建一个查询,将给我每个用户的最新日期?

更新:我忘记了我需要有一个值与最近的日期。


当前回答

如果您的数据库语法支持它,那么TOP 1 WITH TIES可以与rownumber结合使用。

对于您提供的示例数据,使用以下查询:

SELECT TOP 1 WITH TIES
  username, date, value
FROM user_log_in_attempts
ORDER BY ROW_NUMBER() OVER (PARTITION BY username ORDER BY date DESC)

它的收益率:

username | date      | value
-----------------------------
bob      | 8/4/2009  | 1.5
brad     | 2/2/2010  | 1.2
fred     | 12/2/2009 | 1.3

Demo

工作原理:

Rownumber () over (partition by…ORDER BY…)对于每个用户名,计算从最年轻的(rownumber=1)到最老的(rownumber=high)的行列表。 按行号排序…将每个用户最年轻的行排序到最上面,然后是每个用户第二年轻的行,依此类推 因为每个用户都有一个最年轻的行,这些最年轻的行在排序条件的意义上是相等的(都有rownumber=1)。所有最年轻的行都将返回。

用SQL-Server测试。

其他回答

SELECT *     
FROM MyTable T1    
WHERE date = (
   SELECT max(date)
   FROM MyTable T2
   WHERE T1.username=T2.username
)

对于Oracle,将结果集按降序排序,并获取第一个记录,因此您将获得最新的记录:

select * from mytable
where rownum = 1
order by date desc

您可以使用聚合函数MAX和GROUP BY

SELECT username, MAX(date), value FROM tablename GROUP BY username, value
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 username, value
FROM t
WHERE NOT EXISTS (
  SELECT *
  FROM t AS witness
  WHERE witness.username = t.username AND witness.date > t.date
);

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

内连接

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;

左外连接

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