Mysql中的Lead and Lag函数 [英] Lead and Lag function in Mysql

问题描述

我在mysql的 临时 表中有两列case_id和assigned_date.由于没有直接功能来获得超前和滞后，同样在mysql中，oracle也是如此.想要针对每个生成的案例ID获得分配日期的超前和滞后两个值.如何做到这一点.附件是我的样本数据.

I have two columns case_id and assigned_date in my temporary table in mysql. As there is no direct function to get lead and lag likewise oracle in mysql. Want to get lead and lag both values for assigned date against each case id generated . How to achieve that. Attached is my sample data.

case_id                                  assigned_date
41c19f76-e52e-c4c9-62c2-573c71ec5d50    2016-05-18 14:08:14.0
41c19f76-e52e-c4c9-62c2-573c71ec5d51    2016-05-18 14:25:22.0
41c19f76-e52e-c4c9-62c2-573c71ec5d50    2016-05-18 14:26:01.0
41c19f76-e52e-c4c9-62c2-573c71ec5d50    2016-05-19 07:19:13.0
41c19f76-e52e-c4c9-62c2-573c71ec5d50    2016-05-19 07:53:09.0
41c19f76-e52e-c4c9-62c2-573c71ec5d51    2016-05-19 08:18:01.0
41c19f76-e52e-c4c9-62c2-573c71ec5d50    2016-05-19 12:12:35.0

推荐答案

您可以使用 correlated 子查询:

SELECT t1.case_id, t1.assigned_date,
       (SELECT t2.assigned_date
       FROM mytable 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 mytable 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 mytable AS t1

这篇关于Mysql中的Lead and Lag函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！