虽然gordon的建议应该有效（分配与表名相同的别名，@gordon？），但它的速度会非常慢（o（n^2））。

CREATE FUNCTION maxgap()
RETURNS VARCHAR(30)
READS SQL DATA
BEGIN
   DECLARE prevtime INT;
   DECLARE prevevent VARCHAR(20) DEFAULT '';
   DECLARE curtime INT;
   DECLARE curevent VARCHAR(20);
   DECLARE maxtime INT;
   DECLARE maxevent VARCHAR(20);
   DECLARE finished INTEGER DEFAULT 0;

   DECLARE curs CURSOR FOR
      SELECT UNIX_TIMESTAMP(time), event FROM yourtable ORDER BY event,time;
   DECLARE CONTINUE HANDLER 
      FOR NOT FOUND SET finished = 1;

   get_records: LOOP
      FETCH curs INTO curtime, curevent;
      IF finished = 1 THEN 
           LEAVE get_records;
      END IF;
      IF (curevent != prevevent) THEN
         SET prevtime=curtime;
         SET prevevent=curevent;
      END IF 
      IF (maxtime<curtime-prevtime) THEN
         SET maxtime=curtime-prevtime;
         SET maxevent=curevent;
      END IF
    END LOOP get_records;
    CLOSE curs;

    RETURN (CONCAT(maxtime, '/', maxevent));

  END$$

（在一个选择的状态中使用变量应该可以创造出同样的行为——但是一想到它就会让我头疼）。

您需要上一次或下一次的活动。我建议使用子查询：

select t.*
from (select t.*,
             (select t2.time
              from t t2
              where t2.event = t.event and t2.time < t.time
              order by t2.time desc
              limit 1
             ) as prev_time
      from t
     ) t
where prev_time is not null
order by (time - prev_time) desc
limit 1;

假设您没有访问 LEAD 函数可以将表与其自身联接以查找下一行：

SELECT curr.time, curr.event, MIN(next.time) - curr.time AS diff
FROM testdata AS curr
INNER JOIN testdata AS next ON next.event = curr.event AND next.time > curr.time
GROUP BY curr.time, curr.event
ORDER BY diff DESC
LIMIT 1

结果：

+------+-------+------+
| time | event | diff |
+------+-------+------+
|    4 | b     |   11 |
+------+-------+------+

以下是没有排序和限制的结果：

+------+-------+------+
| time | event | diff |
+------+-------+------+
|    1 | a     |    1 |
|    2 | a     |    3 |
|    5 | a     |    2 |
|    1 | b     |    3 |
|    4 | b     |   11 |
+------+-------+------+