oracle 怎么根據自定義月份統計各月資料，例如每月結賬時間為上月26號到本月25號，統計每月營業額
uj5u.com熱心網友回復：
對日期做處理，-25天調整為正常日期樣式后再做處理with mon_yye
as
(
select to_date('20160125','yyyymmdd') date1,100 yye from dual union all
select to_date('20160126','yyyymmdd') ,200 from dual union all
select to_date('20160131','yyyymmdd') ,200 from dual union all
select to_date('20160225','yyyymmdd') ,300 from dual union all
select to_date('20160226','yyyymmdd') ,400 from dual union all
select to_date('20160227','yyyymmdd') ,500 from dual union all
select to_date('20160228','yyyymmdd') ,500 from dual union all
select to_date('20160229','yyyymmdd') ,500 from dual union all
select to_date('20160326','yyyymmdd') ,600 from dual )
select to_char(add_months(a.date1 - 25, 1), 'YYYYMM') date2, sum(a.yye) yye
from mon_yye a
group by to_char(add_months(a.date1 - 25, 1), 'YYYYMM')
order by to_char(add_months(a.date1 - 25, 1), 'YYYYMM')
uj5u.com熱心網友回復：
oracle有個函式，add_months，指定日期增加月select add_months(to_date('2017-01-25','yyyy-mm-dd'),1) from dual 回傳2017-02-25
按照你的需求，那就是統計日期 between to_date('2017-01-25','yyyy-mm-dd') and add_months(to_date('2017-01-25','yyyy-mm-dd'),1)
uj5u.com熱心網友回復：
借#1的資料一用with mon_yye
as
(
select to_date('20160125','yyyymmdd') date1,100 yye from dual union all
select to_date('20160126','yyyymmdd') ,200 from dual union all
select to_date('20160131','yyyymmdd') ,200 from dual union all
select to_date('20160225','yyyymmdd') ,300 from dual union all
select to_date('20160226','yyyymmdd') ,400 from dual union all
select to_date('20160227','yyyymmdd') ,500 from dual union all
select to_date('20160228','yyyymmdd') ,500 from dual union all
select to_date('20160229','yyyymmdd') ,500 from dual union all
select to_date('20160326','yyyymmdd') ,600 from dual )
select t.month_s,SUM(t.yye) from （
select
case when a.date1 between a.date1 and add_months(a.date1,1)+1
then to_char(add_months(a.date1,1),'YYYYMM')
end month_s --如果時間在26到下月25區間內，則取出月份
,a.yye from mon_yye a） t
group by t.month_s
order by t.month_s;
uj5u.com熱心網友回復：
不好意思，剛剛的邏輯有點問題，參考這個吧with mon_yye
as
(
select to_date('20160120','yyyymmdd') date1,100 yye from dual union all
select to_date('20160125','yyyymmdd') date1,100 from dual union all
select to_date('20160126','yyyymmdd') ,200 from dual union all
select to_date('20160131','yyyymmdd') ,200 from dual union all
select to_date('20160225','yyyymmdd') ,300 from dual union all
select to_date('20160226','yyyymmdd') ,400 from dual union all
select to_date('20160227','yyyymmdd') ,500 from dual union all
select to_date('20160228','yyyymmdd') ,500 from dual union all
select to_date('20160229','yyyymmdd') ,500 from dual union all
select to_date('20160326','yyyymmdd') ,600 from dual )
select t.months,sum(t.yye) from (
select case when substr(to_char(a.date1,'YYYYMMDD'),7,2) >=26 then
to_char(add_months(a.date1,1),'YYYYMM')
else to_char(a.date1,'YYYYMM')
end months, --如果日期大于等于26則月份加1，反之保持不變
substr(to_char(a.date1,'YYYYMMDD'),7,2) ,a.date1,a.yye from mon_yye a
) t
group by t.months
order by t.months
uj5u.com熱心網友回復：
學習了。。頂轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/104870.html
標籤：開發
上一篇：怎么能寫好sql陳述句
下一篇：oracle插入m2 變成m?