效果图:
SQL:
select to_char(ADD_MONTHS(TO_DATE(d.time,'YYYY-MM') ,-12),'YYYY-MM')
pretime,d.base as predata,d.time ,d.num as
data,nvl2(d.yoy,concat(d.yoy, '%'),'—') as yoy from (
select c.year||'-'||c.month as time,c.base,c.num,
case c.base when 0 then '' else to_char(round(c.difference/c.base* 100, 2))
end as yoy
from(
select d.year, d.month, d.num,lag(d.num,1) over(partition by d.month order by
d.year) as base,d.num-lag(d.num,1) over(partition by d.month order by
d.year) as difference from(
SELECT substr(a.dt,0,4) year,substr(a.dt,6) month,a.num from (
WITH m AS (
SELECT to_char(ADD_MONTHS(ADD_MONTHS(#{beginDate} ,-12),LEVEL-1),'YYYY-MM') dt
FROM DUAL
CONNECT BY
LEVEL < = MONTHS_BETWEEN(#{endDate},ADD_MONTHS(#{beginDate} ,-12))+1
)
SELECT m.dt,nvl(TEMP.NUM,0) as NUM
FROM m LEFT JOIN
(
select YM, sum(PRINTING_NUM) num from
(select to_char(PRINTING_DATETIME,'YYYY-MM') YM, PRINTING_NUM from INTER_PRINT t
<where>
<if test="department!='' and department!=null ">
and DEPARTMENT = #{department}
</if>
</where>
)tab
Group by tab.YM order by tab.YM
)TEMP ON m.dt=TEMP.YM order by m.dt
)a)d)c)d
where d.time >= to_char(#{beginDate},'YYYY-MM') order by d.time