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 tleft join (select ENTRYID, to_char(CREDATE,'yyyymm') as year_mon, sum(TOTAL) as TOTAL, sum(SETTLEQTY) as SETTLEQTYfrom BMS_SA_DTLQRY_Vgroup by to_char(CREDATE,'yyyymm'),ENTRYID)t1 on to_char(add_months(t.CREDATE,-12),'yyyymm')=t1.year_monleft join ( select ENTRYID, to_char(CREDATE,'yyyy') as yearn, sum(TOTAL) as TOTAL, sum(SETTLEQTY) as SETTLEQTYfrom BMS_SA_DTLQRY_Vgroup 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