MySQL面试题1

eventsevent_typevaluetime252015-05-09 12:42:00

272015-05-09 12:54:39

222015-05-09 14:48:30

3162015-05-09 13:19:57

3202015-05-09 15:01:09

4-422015-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 typefrom(select l1.*, if(@t = l1.event_type, @res := l1.value - @prev, NULL) as res, @prev := l1.value as prev,@t := l1.event_type as typefrom events l1 join (select @t := NULL, @prev := NULL, @res := NULL) initorder by l1.event_type) l2join (select @type := NULL) initwhere res is not nullorder by event_type, time desc) l3where rank < 2;