zsprt.TEST_DATE_OF_AMOUNT兩個欄位 date 和 金額，
--算全年每個月的匯總金額， 但季末的那個月是為全季的累計金額
--例如 1,2,3 三個月，1,2 為當月的匯總金額，3月份 為1,2,3累計的金額
uj5u.com熱心網友回復：
有圖嗎？手機看不到圖片。可以發一下測驗資料，和你的預期結果。
uj5u.com熱心網友回復：
問題比較簡單，以下SQL可以實作：
--1.創建測驗表
create table tmp as
select to_date('2017-01-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-01-05','yyyy-mm-dd') dtime, 200 money from dual union all
select to_date('2017-02-01','yyyy-mm-dd') dtime, 400 money from dual union all
select to_date('2017-02-08','yyyy-mm-dd') dtime, 500 money from dual union all
select to_date('2017-03-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-03-11','yyyy-mm-dd') dtime, 200 money from dual union all
select to_date('2017-03-21','yyyy-mm-dd') dtime, 300 money from dual union all
select to_date('2017-04-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-05-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-06-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-07-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-08-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-09-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-10-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-11-01','yyyy-mm-dd') dtime, 100 money from dual union all
select to_date('2017-12-01','yyyy-mm-dd') dtime, 100 money from dual;
--2.SQL實作
select a.dtime,decode(b.dtime,null,a.money,b.money) money
from( select trunc(dtime,'mm') dtime,sum(money) money from tmp group by trunc(dtime,'mm')) a
left join(select dtime,money
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
from tmp
) where rn = 1
)b
on a.dtime = b.dtime
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/77976.html
標籤：認證與考試