mysql取上个月的起始日期,如何在MySQL中根据开始日期和结束日期获取运行总计...

I have sample data like the below .

Start_Dt End_Dt Dur_of_months amount

2020-01-01 2020-04-01 4 800

where I have Start date and End Date along Duration of months.

By dividing amount (800) by 4 = 200.

I want to get running total minus (200) for each month .

output :

mon_dt amount

Jan 2020 800

Feb 2020 600

Mar 2020 400

Apr 2020 200

I have some code to increment the months between start date and End date

SELECT ID, DATE_FORMAT(Startdate + INTERVAL n.n MONTH, '%M %Y') AS Dates

FROM dates

JOIN (

SELECT n10.n * 10 + n1.n * 1 AS n

FROM (

SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6

UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9

) n10

CROSS JOIN (

SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6

UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9

) n1

) n ON Startdate + INTERVAL n.n MONTH <= EndDate

ORDER BY ID, Startdate + INTERVAL n.n MONTH

How to add the Running total in this one . can any one suggest me .

解决方案

If you are running MySQL 8.0, you can use a recursive query for this:

with recursive cte as (

select

start_dt,

end_dt,

dur_of_months,

1 lvl,

amount initial_amount,

amount

from mytable

union all

select

start_dt + interval 1 month,

end_dt,

dur_of_months,

lvl + 1,

initial_amount,

initial_amount * (1 - lvl / dur_of_months)

from cte

where start_dt < end_dt

)

select date_format(start_dt, '%M %Y') mon_dt, amount from cte order by start_dt

| mon_dt | amount |

| ------------- | ------ |

| January 2020 | 800 |

| February 2020 | 600 |

| March 2020 | 400 |

| April 2020 | 200 |

In earlier versions, starting from your existing query, you would do:

select

date_format(start_dt + interval n.n month, '%M %Y') as mon_dt,

amount * (1 - n / dur_of_months) amount

from mytable

join (

select n10.n * 10 + n1.n * 1 as n

from (

select 0 n union all select 1 union all select 2 union all select 3

union all select 4 union all select 5 union all select 6

union all select 7 union all select 8 union all select 9

) n10

cross join (

select 0 n union all select 1 union all select 2 union all select 3

union all select 4 union all select 5 union all select 6

union all select 7 union all select 8 union all select 9

) n1

) n on start_dt + interval n.n month <= end_dt

order by start_dt + interval n.n month