MYSQL选择前面的行作为前一行数据

Question

这个表名的例子是merchant_point_log:

id    created_date   merchant_id   point_value   recent_point   status
1    2022-01-02           1           5000           5000        earn 
2    2022-01-02           2           3000           3000        earn
3    2022-01-02           1           3000           3000        redeem

我想在一行中显示与前一个最近点类似的日志记录，如：

id    created_date  merchant_id   status   previous_point   point_value   recent_point   
1    2022-01-02         1         in           0             5000           5000        
2    2022-01-02         1         out         5000           3000           2000        
3    2022-01-02         2         in          0              3000           3000  

如何从所选数据的前一行返回previo_point列？

我已经尝试过这个查询，但仍然不能按预期工作：

select
mpl.id 
, mpl2.recent_point as previous_point
, mpl.point_value
, mpl.recent_point
from merchant_point_log mpl
left join merchant_point_log mpl2 on mpl.created_date = adddate(mpl2.created_date, 1)
order by mpl.id asc
;     

结果是前一个点没有按预期返回，它总是重复相同的值。我使用mysql 5.7版

Answer 1

以下是left join的答案，适用于窗口函数不是选项的旧版本。

select   t.id 
,t.created_date
,case t.status when 'earn' then 'in' else 'out' end as status
,coalesce(t2.recent_point, 0)                       as previous_point
,t.point_value
,t.recent_point
from     t left join t t2 on t2.id = t.id-1
order by t.id

状态>>最近的点中2000>td>4000

id	created_date	前一个点	point_value
1	2022-01-02 00:00:00			在	2000
2	2022-01-02 00:00:00	在	5000	7000
3	2022-02-02 00:00:00	输出	7000	3000

我们使用lag得到previous_point，在previous_pointis null的情况下使用coalesce将其设为0。

select id 
,created_date
,case status when 'earn' then 'in' else 'out' end            as status
,coalesce(lag(recent_point) over (order by created_date), 0) as previous_point
,point_value
,recent_point
from   t

状态>>最近的点中2000>td>4000

id	created_date	前一个点	point_value
1	2022-01-02 00:00:00			在	2000
2	2022-01-02 00:00:00	在	5000	7000
3	2022-02-02 00:00:00	输出	7000	3000