我有一个表，它是一个关于用户何时登录的集合条目。
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
其他回答
我使用这种方法获取表中每个用户的最后一条记录。 这是一个查询，根据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 username, date, value
from tablename where (username, date) in (
select username, max(date) as date
from tablename
group by username
)
下面是在SQL Server中只返回每个用户最近记录的一种方法:
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) AS rn
FROM your_table
)
SELECT *
FROM CTE
WHERE rn = 1;
这使用一个公共表表达式(CTE)为每个用户的每条记录分配一个唯一的rn(行号)，基于user_id并按日期降序排序。最后一个查询只选择rn等于1的记录，这表示每个用户最近的记录。
我为我的申请做了一些事情:
查询结果如下:
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 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
