mysql - 找出最新和第二个最新术语之间的区别

Question

表的结构是这样的

create table events(
  event_type integer not null,
  value integer not null,
  time timestamp not null,
  unique (event_type ,time)
  );

插入了一些值，例如

insert into events values
(2,   5,  '2015-05-09 12:42:00'),
(4, -42,  '2015-05-09 13:19:57'),
(2,   2,  '2015-05-09 14:48:39'),
(2,   7,  '2015-05-09 13:54:39'),
(3,  16,  '2015-05-09 13:19:57'),
(3,  20,  '2015-05-09 15:01:09') 

我想为每个已多次注册的 event_type 编写一个查询，返回最新值和第二个最新值之间的差异。

给定以上数据，输出应该是这样的

event_type value
2          -5
3           4

我可以使用以下查询找出最低和最高。这是我能达到的程度..在更远的路上感到困惑。

select event_type,value,time from events group by event_type order by event_type desc;

select * from events group by event_type order by event_type ;

Answer 1

我修改/简化了 Shadkhan 的解决方案。适用于 MySQL、Sqlite、PostgreSQL

fiddle

SELECT e1.event_type, (e1.value - e2.value) AS value FROM
events e1
JOIN
events e2
ON
e1.event_type = e2.event_type
AND
e1.time = (SELECT time FROM events temp1 WHERE
           temp1.event_type=e1.event_type ORDER BY time DESC LIMIT 1)
AND
e2.time = (SELECT time FROM events temp2 WHERE
           temp2.event_type=e2.event_type ORDER BY time DESC LIMIT 1 OFFSET 1)