我想是这样的。(请原谅我的语法错误;在这一点上，我习惯使用HQL !)

编辑:也误解了问题!修正了查询…

SELECT UserId, Value
FROM Users AS user
WHERE Date = (
    SELECT MAX(Date)
    FROM Users AS maxtest
    WHERE maxtest.UserId = user.UserId
)

我想是这样的。(请原谅我的语法错误;在这一点上，我习惯使用HQL !)

编辑:也误解了问题!修正了查询…

SELECT UserId, Value
FROM Users AS user
WHERE Date = (
    SELECT MAX(Date)
    FROM Users AS maxtest
    WHERE maxtest.UserId = user.UserId
)

我不知道你的列的确切名称，但它应该是这样的:

SELECT userid, value
FROM users u1
WHERE date = (
    SELECT MAX(date)
    FROM users u2
    WHERE u1.userid = u2.userid
)

以下查询可以工作:

SELECT user_id, value, date , row_number() OVER (PARTITION BY user_id ORDER BY date desc) AS rn
FROM table_name
WHERE rn= 1

(T-SQL)首先获取所有用户及其最大日期。与表连接以查找maxdates上用户的对应值。

create table users (userid int , value int , date datetime)
insert into users values (1, 1, '20010101')
insert into users values (1, 2, '20020101')
insert into users values (2, 1, '20010101')
insert into users values (2, 3, '20030101')

select T1.userid, T1.value, T1.date 
    from users T1,
    (select max(date) as maxdate, userid from users group by userid) T2    
    where T1.userid= T2.userid and T1.date = T2.maxdate

结果:

userid      value       date                                    
----------- ----------- -------------------------- 
2           3           2003-01-01 00:00:00.000
1           2           2002-01-01 00:00:00.000

使用代码:

select T.UserId,T.dt from (select UserId,max(dt) 
over (partition by UserId) as dt from t_users)T where T.dt=dt;

这将检索结果，而不考虑UserId的重复值。 如果你的UserId是唯一的，它变得更简单:

select UserId,max(dt) from t_users group by UserId;