How to realize the function of lead and lag by mysql
This article focuses on "how to achieve the lead and lag functions of mysql", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "how to achieve mysql lead and lag function function" bar!
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)
At this point, I believe you have a deeper understanding of "how to achieve the lead and lag function functions of mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
