如何从 Mysql 查询获取下一行值

Question

我想使用 SQLYog 从 MySql 查询中获取(选择(下一行值，使用 同样的id_voyage_order？

例：

id_timesheet | id_voyage_order | Duration
----------------------------------------
1            | 106             | 0.00
2            | 106             | 24.00
3            | 210             | 12.00
4            | 106             | 12.00
5            | 210             | 24.00

我想选择它并根据相同的id_voyage顺序在查看文件中制作 Yii 变成这样，并将最后一条记录的持续时间设置为 0 ：

id_timesheet | id_voyage_order | Duration
1.           | 106             | 24.00
2.           | 106             | 12.00
4.           | 106             | 0.00

Answer 1

例如：

SELECT a.id_timesheet
, a.id_voyage_order
, COALESCE(b.duration,0) duration
FROM my_table a
LEFT
JOIN  
( SELECT x.*
, MAX(y.id_timesheet) previous
FROM my_table x 
JOIN my_table y 
ON y.id_voyage_order = x.id_voyage_order 
AND y.id_timesheet < x.id_timesheet 
GROUP 
BY x.id_timesheet
) b
ON b.previous = a.id_timesheet
WHERE a.id_voyage_order = 106;