下一行的MySQL条件查询

Question

我在 MySQL 中有一个名为 Session 的表，如下所示:

+-----+---------------------+---------------------+--------------------+
| id  | start               | finish              | name               |
+-----+---------------------+---------------------+--------------------+
| -12 | 2013-04-27 09:00:00 | 2013-04-27 13:00:00 | Saturday Setup 1   |
| -11 | 2013-04-27 13:00:00 | 2013-04-27 18:00:00 | Saturday Setup 2   |
| -10 | 2013-04-27 23:00:00 | 2013-04-28 08:00:00 | Saturday Night     |
|  -3 | 2013-04-28 08:00:00 | 2013-04-28 13:00:00 | Sunday Setup 1     |
|  -2 | 2013-04-28 13:00:00 | 2013-04-28 18:00:00 | Sunday Setup 2     |
|  -1 | 2013-04-28 23:00:00 | 2013-04-29 08:00:00 | Sunday Night       |
|   1 | 2013-04-29 09:00:00 | 2013-04-29 13:00:00 | Monday Setup 1     |
|   2 | 2013-04-29 13:00:00 | 2013-04-29 17:00:00 | Monday Setup 2     |
|   3 | 2013-04-29 17:00:00 | 2013-04-29 21:00:00 | Monday Setup 3     |
|   4 | 2013-04-29 23:00:00 | 2013-04-30 08:00:00 | Monday Night       |
|  10 | 2013-04-30 09:00:00 | 2013-04-30 13:00:00 | Tuesday Setup 1    |
|  11 | 2013-04-30 13:00:00 | 2013-04-30 17:00:00 | Tuesday Setup 2    |
|  12 | 2013-04-30 23:00:00 | 2013-05-01 08:00:00 | Tuesday Night      |
more rows etc...

我想创建一个查询，该查询将选择表中的所有列，另外一列显示按 Start 排序的同一天的下一个 ID(如果有的话)。

结果是这样的:

+-----+--------+---------------------+---------------------+--------------------+
| id  | nextid | start               | finish              | name               |
+-----+--------+---------------------+---------------------+--------------------+
| -12 |    -11 | 2013-04-27 09:00:00 | 2013-04-27 13:00:00 | Saturday Setup 1   |
| -11 |    -10 | 2013-04-27 13:00:00 | 2013-04-27 18:00:00 | Saturday Setup 2   |
| -10 |        | 2013-04-27 23:00:00 | 2013-04-28 08:00:00 | Saturday Night     |
|  -3 |     -2 | 2013-04-28 08:00:00 | 2013-04-28 13:00:00 | Sunday Setup 1     |
|  -2 |     -1 | 2013-04-28 13:00:00 | 2013-04-28 18:00:00 | Sunday Setup 2     |
|  -1 |        | 2013-04-28 23:00:00 | 2013-04-29 08:00:00 | Sunday Night       |
|   1 |      2 | 2013-04-29 09:00:00 | 2013-04-29 13:00:00 | Monday Setup 1     |
|   2 |      3 | 2013-04-29 13:00:00 | 2013-04-29 17:00:00 | Monday Setup 2     |
|   3 |      4 | 2013-04-29 17:00:00 | 2013-04-29 21:00:00 | Monday Setup 3     |
|   4 |        | 2013-04-29 23:00:00 | 2013-04-30 08:00:00 | Monday Night       |
|  10 |     11 | 2013-04-30 09:00:00 | 2013-04-30 13:00:00 | Tuesday Setup 1    |
|  11 |     12 | 2013-04-30 13:00:00 | 2013-04-30 17:00:00 | Tuesday Setup 2    |
|  12 |        | 2013-04-30 23:00:00 | 2013-05-01 08:00:00 | Tuesday Night      |
more rows etc...

如有任何建议，我们将不胜感激......

Answer 1

它不会非常有效，因为索引不能用于评估自连接标准 DATE(..) = DATE(..)，但您实际上是在尝试获取groupwise minimum :

SELECT cur.id, nxt.id nextid, cur.start, cur.finish, cur.name
FROM (
  SELECT      s1.*, MIN(s2.start) next
  FROM        Session s1
    LEFT JOIN Session s2
           ON DATE(s1.start) = DATE(s2.start)
          AND s1.start < s2.start
  GROUP BY    s1.start
) cur LEFT JOIN Session nxt ON cur.next = nxt.start

查看 sqlfiddle .