MySQL面试题1

events

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

To a table that has event_type and value, which is the substraction of the last two records' value.

name                   value

2 -5

3 4

select event_type, value 
from (
	select event_type, res as value,
	if(@type = l2.event_type, @rank := @rank + 1, @rank := 1) as rank,
	@type := l2.event_type as type
	from
	(
		select l1.*, 
		if(@t = l1.event_type, @res := l1.value - @prev, NULL) as res, 
			@prev := l1.value as prev,
			@t := l1.event_type as type
		from events l1 
		join (select @t := NULL, @prev := NULL, @res := NULL) init
		order by l1.event_type
	) l2
	join (select @type := NULL) init
	where res is not null
	order by event_type, time desc) l3
where rank < 2;