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

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 * from table1 where lastest_date=(Select Max(lastest_date) from table1 where user=yourUserName)

内部查询将返回当前用户的最新日期,外部查询将根据内部查询结果拉出所有数据。

其他回答

SELECT Username, date, value
 from MyTable mt
 inner join (select username, max(date) date
              from MyTable
              group by username) sub
  on sub.username = mt.username
   and sub.date = mt.date

会解决最新的问题。在大型表上,即使有良好的索引,它也可能工作得不太好。

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

根据我的经验,最快的方法是取表中没有新行的每一行。

另一个优点是所使用的语法非常简单,而且查询的含义相当容易掌握(取所有行,确保所考虑的用户名不存在更新的行)。

不存在

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

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

SELECT username, MAX(date), value FROM tablename GROUP BY username, value
SELECT * FROM TABEL1 WHERE DATE= (SELECT MAX(CREATED_DATE) FROM TABEL1)