mysql - 如何选择last和last but one记录

Question

我有一个包含 3 列的表格 id, type, value 如下图所示。

我想做的是进行查询以获取这种格式的数据:

type     previous  current
month-1  666       999
month-2  200       15
month-3  0         12

我做了这个查询，但它只得到最后一个值

select * 
  from statistics 
 where id in (select max(id) from statistics group by type)    
 order 
    by type

编辑:实例http://sqlfiddle.com/#!9/af81da/1

谢谢!

Answer 1

我会这样写:

select s.*,
       (select s2.value
        from statistics s2
        where s2.type = s.type
        order by id desc
        limit 1, 1
       ) value_prev
from statistics s
where id in (select max(id) from statistics s group by type) order by type;

这对于 statistics(type, id) 上的索引应该相对有效。