- to calculate the sum of the amount of each month throughout the year, but at the end of the month is for the whole season the cumulative amount of
Three months - such as 1, 2, 3, 1, 2 for the sum of the amount during the month, March 1, 2, 3 of the cumulative amount
CodePudding user response:
Have a map? Mobile phone can't see the pictures,Can send the test data, and your desired results,
CodePudding user response:
Problem is simpler, SQL can achieve the following:
- 1. To create a test table
The create table TMP as
The select to_date (' 2017-01-01 ', '- dd yyyy - mm) dtime, 100 money from dual union all
The select to_date (' 2017-01-05 ', '- dd yyyy - mm) dtime, 200 money from dual union all
The select to_date (' 2017-02-01 ', '- dd yyyy - mm) dtime, 400 money from dual union all
The select to_date (' 2017-02-08 ', '- dd yyyy - mm) dtime, 500 money from dual union all
The select to_date (' 2017-03-01 ', '- dd yyyy - mm) dtime, 100 money from dual union all
The select to_date (' 2017-03-11 ', '- dd yyyy - mm) dtime, 200 money from dual union all
The select to_date (' 2017-03-21 ', '- dd yyyy - mm) dtime, 300 money from dual union all
The select to_date (' 2017-04-01 ', '- dd yyyy - mm) dtime, 100 money from dual union all
The select to_date (' 2017-05-01 ', '- dd yyyy - mm) dtime, 100 money from dual union all
The select to_date (' 2017-06-01 ', '- dd yyyy - mm) dtime, 100 money from dual union all
The select to_date (' 2017-07-01 ', '- dd yyyy - mm) dtime, 100 money from dual union all
The select to_date (' 2017-08-01 ', '- dd yyyy - mm) dtime, 100 money from dual union all
The select to_date (' 2017-09-01 ', '- dd yyyy - mm) dtime, 100 money from dual union all
The select to_date (' 2017-10-01 ', '- dd yyyy - mm) dtime, 100 money from dual union all
The select to_date (' 2017-11-01 ', '- dd yyyy - mm) dtime, 100 money from dual union all
The select to_date (' 2017-12-01 ', '- dd yyyy - mm) dtime, 100 money from dual;
SQL realize
- 2.The select a. d. time, decode (b.d time, null, arjun oney, b.m oney) money
The from (select trunc (dtime, 'mm) dtime, sum (money) money from TMP group by trunc (dtime,' mm)) a
Left the join (select dtime, money
The from (select dtime, sum (money) over (partition by to_char (dtime, 'q')) money,
Row_number () over (partition by to_char (dtime, 'q') order by trunc (dtime, 'mm) desc) rn
The from TMP
) where rn=1
B)
On a. d. time=b.d time