您可以使用以下命令获取对上一行的引用:
select h.*,
(select h2.ActionDate
from history h2
where h2.LineId = h.LineId and h2.ActionDate < h.ActionDate
order by h2.ActionDate desc
limit 1
) as prev_ActionDate
from history h;
如果需要完整的行,可以使用join
获取数据:
select h.*, hprev.*
from (select h.*,
(select h2.ActionDate
from history h2
where h2.LineId = h.LineId and h2.ActionDate < h.ActionDate
order by h2.ActionDate desc
limit 1
) as prev_ActionDate
from history h
) h left join
history hprev
on hprev.LineId = h.LineId and hprev.ActionDate = h.prev_ActionDate;