【笔记】一条SQL，求同比

效果图：

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 &gt;= to_char(#{beginDate},'YYYY-MM') order by d.time