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

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

其他回答

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

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

不存在

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
SELECT *     
FROM MyTable T1    
WHERE date = (
   SELECT max(date)
   FROM MyTable T2
   WHERE T1.username=T2.username
)

我为我的申请做了一些事情:

查询结果如下:

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));    
SELECT *
FROM ReportStatus c
inner join ( SELECT 
  MAX(Date) AS MaxDate
  FROM ReportStatus ) m
on  c.date = m.maxdate

我的小汇编

自连接优于嵌套选择 但是group by不提供主键,这对于join来说更可取 这个键可以通过分区by和first_value (docs)一起给出

这里有一个查询:

select
 t.*
from 
 Table t inner join (
  select distinct first_value(ID) over(partition by GroupColumn order by DateColumn desc) as ID
  from Table
  where FilterColumn = 'value'
 ) j on t.ID = j.ID

优点:

使用where语句使用任意列筛选数据 从筛选的行中选择任意列

缺点:

需要MS SQL Server从2012年开始。