如何查询sql以获取每个用户的最新记录日期-Java 学习之路

151

我有一个表，它是用户登录时的集合条目 .

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

如何创建一个可以为每个用户提供最新日期的查询？

Update: 我忘记了我需要一个与最新日期一致的值 .

21 回答

265

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

回复于 2024-05-05T06:26:00+08:00

0

Select * from table1 where lastest_date=(select Max(latest_date) from table1 where user=yourUserName)

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

回复于 2024-05-05T06:26:00+08:00

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

将解决更新的问题 . 即使具有良好的索引，它在大型表上也可能无法正常工作 .

回复于 2024-05-05T06:26:00+08:00

SELECT *
FROM ReportStatus c
inner join ( SELECT 
  MAX(Date) AS MaxDate
  FROM ReportStatus ) m
on  c.date = m.maxdate

回复于 2024-05-05T06:26:00+08:00

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

回复于 2024-05-05T06:26:00+08:00

使用窗口函数（适用于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

回复于 2024-05-05T06:26:00+08:00

-1

我看到大多数开发人员使用内联查询而不考虑它对大数据的影响 .

简单地说，你可以通过以下方式实现

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;

回复于 2024-05-05T06:26:00+08:00

要获取包含用户最大日期的整行：

select username, date, value
from tablename where (username, date) in (
    select username, max(date) as date
    from tablename
    group by username
)

回复于 2024-05-05T06:26:00+08:00

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

回复于 2024-05-05T06:26:00+08:00

2
这个应该为您编辑的问题提供正确的结果 .

子查询确保只查找最新日期的行，而外部 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
```
回复于 2024-05-05T06:26:00+08:00
1
对于Oracle按降序对结果集进行排序并获取第一条记录，因此您将获得最新记录：
```
select * from mytable
where rownum = 1
order by date desc
```
回复于 2024-05-05T06:26:00+08:00

-1

我用这种方式为我桌上的每个用户记录最后一条记录 . 这是根据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

回复于 2024-05-05T06:26:00+08:00

SELECT * FROM TABEL1 WHERE DATE= (SELECT MAX(CREATED_DATE) FROM TABEL1)

回复于 2024-05-05T06:26:00+08:00

0
我的小编辑
- self join 比嵌套好 select
- 但 group by 没有给你 primary key 这是 join 的首选
- 此键可以由 partition 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 语句过滤数据
- select 过滤行中的任何列
缺点：
- 需要从2012年开始的MS SQL Server .
回复于 2024-05-05T06:26:00+08:00

我做了一些我的应用程序：

以下是查询：

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

回复于 2024-05-05T06:26:00+08:00

0
根据我的经验，最快的方法是获取表中没有新行的每一行 . 这里有一些基准，我手边有一些数据 .

另一个优点是使用的语法非常简单，并且查询的含义相当容易掌握（占用所有行，以便不考虑用户名的新行） .

NOT EXISTS
```
SELECT username, value
FROM t
WHERE NOT EXISTS (
  SELECT *
  FROM t AS witness
  WHERE witness.date > t.date
);
```
解释总费用：2.38136

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
```
总费用：61.5823

INNER JOIN
```
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;
```
解释总费用：67.5439

LEFT OUTER JOIN
```
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
```
解释总费用：62.964

解释计划来自一个大约有10k行的数据库，存储为XML . 使用的查询还包含谓词“group_id ='1'” .
回复于 2024-05-05T06:26:00+08:00
23
这类似于上面的答案之一，但在我看来，它更简单，更整洁 . 此外，显示了交叉应用语句的良好用途 . 对于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
```
回复于 2024-05-05T06:26:00+08:00

您还可以使用分析秩功能

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

回复于 2024-05-05T06:26:00+08:00

您将使用聚合函数MAX和GROUP BY

SELECT username, MAX(date), value FROM tablename GROUP BY username, value

回复于 2024-05-05T06:26:00+08:00

-1

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

回复于 2024-05-05T06:26:00+08:00

0
这也应该用于获取用户的所有最新条目 .
```
SELECT username, MAX(date) as Date, value
FROM MyTable
GROUP BY username, value
```
回复于 2024-05-05T06:26:00+08:00

如何查询sql以获取每个用户的最新记录日期

21 回答

NOT EXISTS

ROW_NUMBER

INNER JOIN

LEFT OUTER JOIN

相关问题