MySQL - 为列表中的每个 ID 选择 2 个最后一个元素

Question

我有一个具有以下架构的表:

id, field, time
3, 'test0', 2012-08-29
3, 'test1', 2012-08-30
3, 'test2', 2012-08-31
2, 'test0', 2012-08-19
2, 'test1', 2012-08-20
2, 'test2', 2012-08-26
...

我需要为列表中的每个 id 找到它的最后一个值和上一个值。例如，如果 ids = [2,3] 结果应该返回

3, 'test1', 2012-08-30
3, 'test2', 2012-08-31 
2, 'test1', 2012-08-20
2, 'test2', 2012-08-26

如果我只需要最后一个值，我会使用

SELECT *
FROM table
WHERE id IN (2, 3)
GROUP BY id

有什么办法可以实现吗？

Answer 1

如果您的每个 ID 号的时间都是唯一的，这将有效。

SELECT d.id, d.field,d.time
  FROM d d
  JOIN (
    SELECT max(d.time)m, d.id
      FROM d
      JOIN (
         SELECT max(time) m, id
           FROM d
       GROUP BY id
      )e ON d.id=e.id AND e.m>d.time
      GROUP BY d.id
   )e ON d.time >= e.m AND d.id = e.id

这是它的工作原理。此嵌套查询获取每个 id 的最新时间。

         SELECT max(time) m, id
           FROM d
       GROUP BY id

然后，依次将其嵌套到该查询中，为您提供每个 id 的倒数第二个时间(排除最新时间的行子集的最新时间)。

    SELECT max(d.time)m, d.id
      FROM d
      JOIN (
         SELECT max(time) m, id
           FROM d
       GROUP BY id
      )e ON d.id=e.id AND e.m > d.time

最后，完整查询(如上图所示)获取时间大于或等于第二个最新时间的所有行。

如果您的时间不是唯一的——也就是说，您有一些行出现了相同的 ID 和时间，您可能会为每个 ID 获得超过两行。但是你总是会得到两个最新的时间。

如果特定 ID 只有一行，您将无法获取它。

只有您知道这些限制是否可以接受。

去 fiddle 吧! http://sqlfiddle.com/#!2/82719/5/0