mysql - 最新和第二最新的列值

Question

我想查找组类型上最新和第二最新的列值差异。并且类型计数必须大于1。

考虑我的表格中的示例数据:

type  value        date
--------------------------------
 2       5   2015-05-09 12:42:00
 4     -42   2015-05-09 13:19:57
 2       2   2015-05-09 14:48:30
 2       7   2015-05-09 12:54:39
 3      16   2015-05-09 13:19:57
 3      20   2015-05-09 15:01:09

我希望结果是

type   Value   
---------------------
  2     -5
  3      4

即对于类型2，最新的值为2，第二个最新的值为7，所以它们之间的差为-5。

Answer 1

您可以使用变量来减少所需的表扫描次数:

SELECT   type, 
         value
FROM (  SELECT   @n := IF(type = @t,  @n+1, 1) as num,
                 @t := type                    as type,
                 @v - value                    as value,
                 @v := value
        FROM     mytable
        ORDER BY type, 
                 date DESC) base
WHERE   num = 2

SQL Fiddle

这个想法是为记录提供一个记录编号，对于 type 的每个不同值，再次从 1 开始，然后随着日期值的递减而增加。

按类型顺序和日期降序浏览记录时，三个变量保持更新:

• @n:赋予前一条记录的记录号；
• @t:上一条记录中type列的值；
• @v:之前记录中value列的值；

所有三个更新如下:

如果当前记录的type值与前一条记录中的相同，则@n增加1，否则重置为1。

对于每条记录，还返回当前记录和前一个记录的值之间的差异。

类型和值更新以反射(reflect)当前记录的值。

最后，结果集中仅保留编号为 2 的记录。