select to_char(t.CREDATE,'mm') as CREDATE, t.ENTRYID, t2.ENTRYID, t2.ENTRYID, nvl(t1.TOTAL,0) as "同期TOTAL",--同期 nvl(t1.SETTLEQTY,0) as "同期SETTLEQTY",--同期 nvl(t2.TOTAL ,0)as "去年TOTAL",--去年 nvl(t2.SETTLEQTY,0) as "去年SETTLEQTY",--去年 to_char(t.CREDATE,'yyyy-mm') as YEARN, to_char(t.CREDATE,'yyyy') as YEAR, nvl(sum(t.TOTAL),0) as TOTAL,--当月 nvl(sum(t.SETTLEQTY),0) as SETTLEQTY--当月 from BMS_SA_DTLQRY_V t left join ( select ENTRYID, to_char(CREDATE,'yyyymm') as year_mon, sum(TOTAL) as TOTAL, sum(SETTLEQTY) as SETTLEQTY from BMS_SA_DTLQRY_V group by to_char(CREDATE,'yyyymm'),ENTRYID )t1 on to_char(add_months(t.CREDATE,-12),'yyyymm')=t1.year_mon left join ( select ENTRYID, to_char(CREDATE,'yyyy') as yearn, sum(TOTAL) as TOTAL, sum(SETTLEQTY) as SETTLEQTY from BMS_SA_DTLQRY_V group by to_char(CREDATE,'yyyy'),ENTRYID )t2 on to_char(add_months(t.CREDATE,-12),'yyyy')=t2.yearn
(select DECODE(sum(t.TOTAL), 0,SUM(t2.TOTAL),0, （）) from BMS_SA_DTLQRY_V ) group by to_char(CREDATE,'mm'), to_char(t.CREDATE,'yyyy-mm'), to_char(CREDATE,'yyyy'), t1.TOTAL, t.ENTRYID, t1.SETTLEQTY, t2.TOTAL, t2.SETTLEQTY, t2.ENTRYID, t2.ENTRYID |
1min目标场景问卷
立即参与
最佳回答 |
||||
|
1
|
|
|||
