mysql - 在同一列中的两行中两次之间的差异 - MySql

如何做到以下几点
餐桌贝斯特伦根

id      abholdatum         abholzeit

1       2014-02-03         03:35:00
2       2014-02-03         08:30:00
3       2014-02-03         05:10:00
4       2014-02-03         15:25:00
5       2014-02-03         11:50:00

我想要这个结果

id      abholdatum         abholzeit endzeit       diff

1       2014-02-03         03:35:00  05:10:00      5700
3       2014-02-03         05:10:00  08:30:00      12000
2       2014-02-03         08:30:00  11:50:00      12000
5       2014-02-03         11:50:00  15:25:00      12900
4       2014-02-03         15:25:00  00:00:00      0

有人能告诉我怎么解决这个问题吗
谢谢

最佳答案

首先，您需要按如下方式创建透视表。
你可以在这里测试http://www.sqlfiddle.com/#!2/d62d4/3

SELECT x.id, x.abholdatum, x.abholzeit AS from_ts, y.abholzeit AS to_ts
FROM (
    SELECT @seq := @seq + 1 AS ord, id, abholdatum, abholzeit
    FROM tbl, (SELECT @seq := 0) init
    ORDER BY abholzeit
) x LEFT JOIN  (

    SELECT @seq2 := @seq2 + 1 AS ord, id, abholdatum, abholzeit
    FROM tbl, (SELECT @seq2 := 0) init
    ORDER BY abholzeit
) y ON x.ord = y.ord - 1;
+------+------------+----------+----------+
| id   | abholdatum | from_ts  | to_ts    |
+------+------------+----------+----------+
|    1 | 2014-02-03 | 03:35:00 | 05:10:00 |
|    3 | 2014-02-03 | 05:10:00 | 08:30:00 |
|    2 | 2014-02-03 | 08:30:00 | 11:50:00 |
|    5 | 2014-02-03 | 11:50:00 | 15:25:00 |
|    4 | 2014-02-03 | 15:25:00 | NULL     |
+------+------------+----------+----------+
5 rows in set (0.00 sec)

其次，让我们计算时间场的差。

SELECT x.id, x.abholdatum, x.abholzeit AS from_ts, y.abholzeit AS to_ts,
TO_SECONDS(CONCAT(x.abholdatum, ' ', y.abholzeit)) - TO_SECONDS(CONCAT(x.abholdatum, ' ', x.abholzeit)) AS diff_ts
FROM (
    SELECT @seq := @seq + 1 AS ord, id, abholdatum, abholzeit
    FROM tbl, (SELECT @seq := 0) init
    ORDER BY abholzeit
) x LEFT JOIN  (

    SELECT @seq2 := @seq2 + 1 AS ord, id, abholdatum, abholzeit
    FROM tbl, (SELECT @seq2 := 0) init
    ORDER BY abholzeit
) y ON x.ord = y.ord - 1;
+------+------------+----------+----------+---------+
| id   | abholdatum | from_ts  | to_ts    | diff_ts |
+------+------------+----------+----------+---------+
|    1 | 2014-02-03 | 03:35:00 | 05:10:00 |    5700 |
|    3 | 2014-02-03 | 05:10:00 | 08:30:00 |   12000 |
|    2 | 2014-02-03 | 08:30:00 | 11:50:00 |   12000 |
|    5 | 2014-02-03 | 11:50:00 | 15:25:00 |   12900 |
|    4 | 2014-02-03 | 15:25:00 | NULL     |    NULL |
+------+------------+----------+----------+---------+
5 rows in set (0.00 sec)