选择不同的行，同时按最大值分组

I currently have the following table:

我目前有下表:

ID   |  Name    |  EventTime            |  State
1001 |  User 1  |  2013/07/22 00:00:05  |  15
1002 |  User 2  |  2013/07/23 00:10:00  |  100
1003 |  User 3  |  2013/07/23 06:15:31  |  35
1001 |  User 1  |  2013/07/23 07:13:00  |  21
1001 |  User 1  |  2013/07/23 08:15:00  |  25
1003 |  User 3  |  2013/07/23 10:00:00  |  22
1002 |  User 2  |  2013/07/23 09:18:21  |  50

What I need is the state for each distinct userid from the last eventtime similar to below:

我需要的是上次事件时间中每个不同用户标识的状态,类似于以下内容:

ID   |  Name    |  EventTime            |  State
1001 |  User 1  |  2013/07/23 08:15:00  |  25
1003 |  User 3  |  2013/07/23 10:00:00  |  22
1002 |  User 2  |  2013/07/23 09:18:21  |  50

I need something similar to the following but I can't quite get what I need.

我需要类似下面的东西,但我不能得到我需要的东西。

SELECT ID, Name, max(EventTime), State
FROM MyTable
GROUP BY ID

4 个解决方案

#1

In databases that support analytic functions, you could use row_number():

在支持分析函数的数据库中,您可以使用row_number():

select  *
from    (
        select  row_number() over (partition by ID 
                                   order by EventTime desc) as rn
        ,       *
        from    YourTable
        ) as SubQueryAlias
where   rn = 1

#2

SELECT
ID, Name, EventTime, State
FROM
MyTable mt
WHERE EventTime = (SELECT MAX(EventTime) FROM MyTable sq WHERE mt.ID = sq.ID)

#3

You did not specify what database you are using but you should be able to use an aggregate function in a subquery to get the max event time for each id:

您没有指定要使用的数据库,但是您应该能够在子查询中使用聚合函数来获取每个id的最大事件时间:

select t1.id,
  t1.name,
  t1.eventtime,
  t1.state
from mytable t1
inner join
(
  select max(eventtime) eventtime, id
  from mytable
  group by id
) t2
  on t1.id = t2.id
  and t1.eventtime = t2.eventtime
order by t1.id;

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

#4

You can try this:-

你可以试试这个: -

SELECT ID, Name, EventTime, State
FROM mytable mm Where EventTime IN (Select MAX(EventTime) from mytable mt where mt.id=mm.id)

SQL FIDDLE

#1