This the example of the table name is merchant_point_log :

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

i would like to show logging like with previous recent point in one row like :

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

how do i return previos_point column from before row of selected data ?

i already tried this query but still doesn't work as expected :

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
;     

the result is previous point dont return as expected it always repeat same value. I use mysql version 5.7

CodePudding user response：

Here's an answer with left join for older versions where window functions are not an option.

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

Fiddle

CodePudding user response：

We use lag to get previous_point and coalesce to put it as 0 in case previous_point is null.

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

Fiddle