mysql實現oracle的lead和lag函式功能

MariaDB [test]> select * from t;
+--------------------------------------+---------------------+
| case_id                              | assigned_date       |
+--------------------------------------+---------------------+
| 41c19f76-e52e-c4c9-62c2-573c71ec5d50 | 2016-05-18 14:08:14 |
| 41c19f76-e52e-c4c9-62c2-573c71ec5d51 | 2016-05-18 14:25:22 |
| 41c19f76-e52e-c4c9-62c2-573c71ec5d50 | 2016-05-18 14:26:01 |
| 41c19f76-e52e-c4c9-62c2-573c71ec5d50 | 2016-05-19 07:19:13 |
| 41c19f76-e52e-c4c9-62c2-573c71ec5d50 | 2016-05-19 07:53:09 |
| 41c19f76-e52e-c4c9-62c2-573c71ec5d51 | 2016-05-19 08:18:01 |
| 41c19f76-e52e-c4c9-62c2-573c71ec5d50 | 2016-05-19 12:12:35 |
+--------------------------------------+---------------------+
7 rows in set (0.00 sec)
MariaDB [test]> SELECT t1.case_id, t1.assigned_date,
    ->        (SELECT t2.assigned_date
    ->        FROM t AS t2
    ->        WHERE t2.case_id = t1.case_id AND
    ->              t2.assigned_date > t1.assigned_date
    ->        ORDER BY t2.assigned_date LIMIT 1) AS next_date,
    ->        (SELECT t2.assigned_date
    ->        FROM t AS t2
    ->        WHERE t2.case_id = t1.case_id AND
    ->              t2.assigned_date < t1.assigned_date
    ->        ORDER BY t2.assigned_date DESC LIMIT 1) AS prev_date
    -> FROM t AS t1;
+--------------------------------------+---------------------+---------------------+---------------------+
| case_id                              | assigned_date       | next_date           | prev_date           |
+--------------------------------------+---------------------+---------------------+---------------------+
| 41c19f76-e52e-c4c9-62c2-573c71ec5d50 | 2016-05-18 14:08:14 | 2016-05-18 14:26:01 | NULL                |
| 41c19f76-e52e-c4c9-62c2-573c71ec5d51 | 2016-05-18 14:25:22 | 2016-05-19 08:18:01 | NULL                |
| 41c19f76-e52e-c4c9-62c2-573c71ec5d50 | 2016-05-18 14:26:01 | 2016-05-19 07:19:13 | 2016-05-18 14:08:14 |
| 41c19f76-e52e-c4c9-62c2-573c71ec5d50 | 2016-05-19 07:19:13 | 2016-05-19 07:53:09 | 2016-05-18 14:26:01 |
| 41c19f76-e52e-c4c9-62c2-573c71ec5d50 | 2016-05-19 07:53:09 | 2016-05-19 12:12:35 | 2016-05-19 07:19:13 |
| 41c19f76-e52e-c4c9-62c2-573c71ec5d51 | 2016-05-19 08:18:01 | NULL                | 2016-05-18 14:25:22 |
| 41c19f76-e52e-c4c9-62c2-573c71ec5d50 | 2016-05-19 12:12:35 | NULL                | 2016-05-19 07:53:09 |
+--------------------------------------+---------------------+---------------------+---------------------+
7 rows in set (0.01 sec)