我如何查询sql为每个用户的最新记录日期

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

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

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

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

1105169 次浏览
小开

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

SELECT username, MAX(date), value FROM tablename GROUP BY username, value
小开 
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

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

小开

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

子查询确保只找到最近日期的行，外层GROUP BY将负责联系。当同一用户的同一日期有两个条目时，它将返回value最高的那个。

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 username, date, value
from tablename where (username, date) in (
select username, max(date) as date
from tablename
group by username
)
小开
最佳答案 
select t.username, t.date, t.value
from MyTable t
inner join (
select username, max(date) as MaxDate
from MyTable
group by username
) tm on t.username = tm.username and t.date = tm.MaxDate
小开

使用窗口函数(适用于Oracle, Postgres 8.4, SQL Server 2005, DB2, Sybase, Firebird 3.0, MariaDB 10.3)

select * from (
select
username,
date,
value,
row_number() over(partition by username order by date desc) as rn
from
yourtable
) t
where t.rn = 1
小开 
SELECT *
FROM ReportStatus c
inner join ( SELECT
MAX(Date) AS MaxDate
FROM ReportStatus ) m
on  c.date = m.maxdate
小开 
SELECT *
FROM MyTable T1
WHERE date = (
SELECT max(date)
FROM MyTable T2
WHERE T1.username=T2.username
)
小开 
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
小开

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

select * from mytable
where rownum = 1
order by date desc
小开 
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 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 * from table1 where lastest_date=(select Max(latest_date) from table1 where user=yourUserName)

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

小开
我使用这种方法获取表中每个用户的最后一条记录。 这是一个查询，根据PDA设备上检测到的最近时间获得销售人员的最后位置 
CREATE FUNCTION dbo.UsersLocation()
RETURNS TABLE
AS
RETURN
Select GS.UserID, MAX(GS.UTCDateTime) 'LastDate'
From USERGPS GS
where year(GS.UTCDateTime) = YEAR(GETDATE())
Group By GS.UserID
GO
select  gs.UserID, sl.LastDate, gs.Latitude , gs.Longitude
from USERGPS gs
inner join USER s on gs.SalesManNo = s.SalesmanNo
inner join dbo.UsersLocation() sl on gs.UserID= sl.UserID and gs.UTCDateTime = sl.LastDate
order by LastDate desc
小开 
SELECT * FROM TABEL1 WHERE DATE= (SELECT MAX(CREATED_DATE) FROM TABEL1)
小开

我的小汇编

  • self join优于嵌套的select
  • 但是group by不会给你primary key，这对于join来说是更可取的
  • 这个键可以由partition by和first_value (文档)一起给出。

这里有一个查询:

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语句过滤数据
  • select过滤行中的任何列

缺点:

  • 需要MS SQL Server从2012年开始。
小开

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

查询结果如下:

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));
小开

这与上面的一个答案相似，但在我看来，它更简单、更整洁。此外，还展示了交叉apply语句的良好用法。SQL Server 2005及以上版本…

select
a.username,
a.date,
a.value,
from yourtable a
cross apply (select max(date) 'maxdate' from yourtable a1 where a.username=a1.username) b
where a.date=b.maxdate
小开

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

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

不存在

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

你也可以使用分析秩函数

    with temp as
(
select username, date, RANK() over (partition by username order by date desc) as rnk from t
)
select username, rnk from t where rnk = 1
小开 
SELECT MAX(DATE) AS dates
FROM assignment
JOIN paper_submission_detail ON  assignment.PAPER_SUB_ID =
paper_submission_detail.PAPER_SUB_ID
小开

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

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

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

< a href = " http://sqlfiddle.com/ !18/9eecb/141361/0" rel="nofollow noreferrer">Demo .

工作原理:

  • ROWNUMBER() OVER (PARTITION BY... ORDER BY...)对于每个用户名，一个行列表从最年轻的(rownumber=1)到最老的(rownumber=high)计算。
  • ORDER BY ROWNUMBER...将每个用户最年轻的行排序到最上面，然后是每个用户第二年轻的行，依此类推
  • TOP 1 WITH TIES因为每个用户都有一个最年轻的行，这些最年轻的行在排序条件的意义上是相等的(都有rownumber=1)。所有最年轻的行都将返回。

用SQL-Server测试。


提交答案