从数据库中检索每个用户的倒数第二条记录 [英] Retrieve the second last record for each user from the database

问题描述

我有一个包含用户名、更新日期和状态的表，如下所示:

I have a table that holds username, updated date, and status as below:

akg     29-NOV-10       Active
akg     13-JAN-12       NonActive
akg     10-MAR-12       Active
ems     23-JUL-12       NonActive
ems     10-SEP-10       Active
tkp     10-SEP-10       NonActive
tkp     13-DEC-10       Active
tkp     02-JUL-12       NonActive
tkp     24-SEP-10       Active
aron    12-JAN-11       NonActive
aron    07-NOV-11       Active
aron    25-JUN-12       NonActive

在此表中，每次我们更改状态时都会更新用户状态(即，username 可以有许多条目，如表中所示.

In this table user status is updated every time we change the status (ie a username can have many entries as shown in the table.

我想要每个用户的倒数第二个更新记录.即对于上表，结果应该是:

I would like the second last updated record for each user. ie for the above table, the result should be:

akg     13-JAN-12       NonActive
ems     10-SEP-10       Active
tkp     13-DEC-10       Active
aron    07-NOV-11       Active

我真的很困惑，因为我想获得每个用户的记录.

I'm really confused as I want to get the record of each users in this.

有什么可以用于此的查询吗?

Is there any query which can be used for this?

谢谢

推荐答案

你可以试试这个，虽然有点冗长，但确实有效:

You could try that, it's a bit verbose but it works:

SELECT
  name,
  max(Updated_on) as Updated_on,
  STATUS
FROM userstatus a
  WHERE (name, Updated_on) not in
  (select name, max(Updated_on) FROM userstatus group by name)
group by name, status
HAVING UPDATED_ON =
  (SELECT MAX(UPDATED_ON) FROM userstatus b where a.name = b.name
   and (b.name, b.Updated_on) not in
  (select name, max(Updated_on) FROM userstatus group by name)
  group by name);

Sqlfiddle

这篇关于从数据库中检索每个用户的倒数第二条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！