SQL 下一个最近的记录

Question

我有表 act_detail:

+------+------+--------+
| id   | name | action |
+------+------+--------+
|    1 | Tom  | eat    |
|    2 | Jack | eat    |
|    3 | Tom  | play   |
|    4 | Tom  | sleep  |
|    5 | Tom  | eat    |
|    6 | Jack | sleep  |
|    7 | Tom  | sleep  |
|    8 | Tom  | eat    |
+------+------+--------+

我想要获取“吃”的夏季信息和下一个最近的同名“ sleep ”信息:

+------+--------+----------+
| name | eat_id | sleep_id |
+------+--------+----------+
| Tom  |      1 |        4 |
| Jack |      2 |        6 |
| Tom  |      5 |        7 |
| Tom  |      8 |     NULL |
+------+--------+----------+

我发现我可以用下面的SQL得到结果:

SELECT 
    a.name, 
    a.id AS eat_id, 
    (SELECT MIN(id) FROM act_detail b WHERE a.name = b.name AND b.id > a.id AND b.action = 'sleep') AS sleep_id 
FROM act_detail a 
WHERE a.action = 'eat'
ORDER BY a.id;

但是这个SQL需要子查询，并且当需要获取表b中更多列时需要更多子查询。如果记录很多，速度会很慢。
假设我们可以添加任何索引。有没有什么有效的方法可以用标准SQL解决这个问题(也许一个左连接，一个临时表和一个group by语句)？

Answer 1

没有子查询:-

SELECT a.name, a.id AS eat_id, MIN(b.id) AS sleep_id
FROM act_detail a
LEFT OUTER JOIN act_detail b 
ON a.name = b.name
AND b.action = 'sleep'
AND b.id > a.id
WHERE a.action = 'eat'
GROUP BY a.name, eat_id
ORDER BY a.id;

在这里进行 SQL 操作:-

http://www.sqlfiddle.com/#!2/11834/2