表:
UserId, Value, Date.
我想获得UserId,为每个UserId的最大值(日期)的值。也就是说,具有最新日期的每个UserId的值。有没有一种方法可以在SQL中简单地做到这一点?(最好是Oracle)
更新:为任何歧义道歉:我需要得到所有的用户id。但是对于每个UserId,只有该用户拥有最新日期的行。
表:
UserId, Value, Date.
我想获得UserId,为每个UserId的最大值(日期)的值。也就是说,具有最新日期的每个UserId的值。有没有一种方法可以在SQL中简单地做到这一点?(最好是Oracle)
更新:为任何歧义道歉:我需要得到所有的用户id。但是对于每个UserId,只有该用户拥有最新日期的行。
当前回答
(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
其他回答
MySQL没有分区KEEP, DENSE_RANK概念的解决方案。
select userid,
my_date,
...
from
(
select @sno:= case when @pid<>userid then 0
else @sno+1
end as serialnumber,
@pid:=userid,
my_Date,
...
from users order by userid, my_date
) a
where a.serialnumber=0
参考:http://benincampus.blogspot.com/2013/08/select-rows-which-have-maxmin-value-in.html
SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)
FROM table
GROUP BY userid
Select
UserID,
Value,
Date
From
Table,
(
Select
UserID,
Max(Date) as MDate
From
Table
Group by
UserID
) as subQuery
Where
Table.UserID = subQuery.UserID and
Table.Date = subQuery.mDate
在PostgreSQL 8.4或更高版本中,你可以这样使用:
select user_id, user_value_1, user_value_2
from (select user_id, user_value_1, user_value_2, row_number()
over (partition by user_id order by user_date desc)
from users) as r
where r.row_number=1
我想这应该有用吧?
Select
T1.UserId,
(Select Top 1 T2.Value From Table T2 Where T2.UserId = T1.UserId Order By Date Desc) As 'Value'
From
Table T1
Group By
T1.UserId
Order By
T1.UserId