SELECT 'title'as dataType, '公司' as orgName,'专业' as majorName,'地区' as areaName,
'201703' as DIYI,'201603' as DIER ,'增幅' as growingRate,
'201704' as DISAN,'201604' as DISI ,'增幅' as growingRate,
'201705' as DIWU,'201605' as DILIU ,'增幅' as growingRate,
'本期总费用' as SUMCONT,'上期总费用' as LASTSUMCONT, '总增幅' as YOYANA from dual
union
SELECT 'data'as dataType,max(ORG_NAME) as orgName,max(MAJOR_NAME) as majorName,max(AREA_NAME) as areaName,
TO_CHAR(max(decode(colnum,1,LAST_EXPENSE,null))) DIYI,TO_CHAR(max(decode(colnum,1,EXPENSE,null)))DIER,
NVL(ROUND((max(decode(colnum,1,EXPENSE,null))-max(decode(colnum,1,LAST_EXPENSE,null)))/max(decode(colnum,1,LAST_EXPENSE,null))*100,2),0)||'%' AS growingRate,
TO_CHAR(max(decode(colnum,2,LAST_EXPENSE,null))) DISAN,TO_CHAR(max(decode(colnum,2,EXPENSE,null)))DISI,
NVL(ROUND((max(decode(colnum,2,EXPENSE,null))-max(decode(colnum,2,LAST_EXPENSE,null)))/max(decode(colnum,2,LAST_EXPENSE,null))*100,2),0)||'%' AS growingRate,
TO_CHAR(max(decode(colnum,3,LAST_EXPENSE,null))) DIWU,TO_CHAR(max(decode(colnum,3,EXPENSE,null))) DILIU,
NVL(ROUND((max(decode(colnum,3,EXPENSE,null))-max(decode(colnum,3,LAST_EXPENSE,null)))/max(decode(colnum,3,LAST_EXPENSE,null))*100,2),0)||'%' AS growingRate,
to_char(sum(LAST_EXPENSE)),to_char(SUM(EXPENSE)),
NVL(ROUND((SUM(EXPENSE)-SUM(LAST_EXPENSE))/SUM(LAST_EXPENSE)*100,2),0)||'%' AS YOYANA
FROM (
WITH TEMP AS (
SELECT
FM.FEE_YEAR,FM.FEE_MONTH,FM.MAJOR_ID,FM.COUNTY_ID,
FM.AM_ORG_ID,UO.ORG_NAME,EM.MAJOR_NAME,UA.AREA_NAME,nvl(FM.PAYABLE_MONEY,0)+nvl(FM.DEDUCT_MONEY,0)AS EXPENSE,
row_number()over(partition by FM.COUNTY_ID order by FM.FEE_YEAR DESC,FM.FEE_MONTH ) colnum
FROM FE_MT_FEE_FILL FM
INNER JOIN FE_FEE_APPLY FFA ON FM.APPLY_ORDER_ID = FFA.APPLY_ORDER_ID
INNER JOIN OM_ORDER OO ON FFA.ORDER_CODE = OO.ORDER_CODE AND OO.ORDER_STATE ='10F'
LEFT JOIN UOS_ORG UO ON UO.ORG_ID = FM.AM_ORG_ID
LEFT JOIN EOMS_MAJOR EM ON EM.MAJOR_ID = FM.MAJOR_ID
LEFT JOIN UOS_AREA UA ON UA.AREA_ID = FM.COUNTY_ID
WHERE 1=1
AND FM.MAJOR_ID=1 AND FM.AM_ORG_ID = 151
AND FM.COUNTY_ID IN (SELECT UA.AREA_ID FROM UOS_AREA UA WHERE UA.PATH_CODE LIKE '%2.3%')
AND CONCAT(FM.FEE_YEAR,LPAD(FM.FEE_MONTH,2,'0')) IN ('201603','201703','201604','201704','201605','201705')
--GROUP BY FM.COUNTY_ID,FM.FEE_YEAR,FM.FEE_MONTH,FM.PAYABLE_MONEY,FM.DEDUCT_MONEY,FM.MAJOR_ID,FM.AM_ORG_ID,UO.ORG_NAME,EM.MAJOR_NAME,UA.AREA_NAME
)
SELECT T.FEE_YEAR,FEE_MONTH,T.MAJOR_ID,T.AM_ORG_ID,EXPENSE,T.COUNTY_ID,ORG_NAME,MAJOR_NAME,AREA_NAME,
(SELECT EXPENSE as LAST_EXPENSE FROM TEMP
WHERE T.FEE_YEAR-1 = FEE_YEAR AND T.FEE_MONTH=FEE_MONTH) AS LAST_EXPENSE,colnum
FROM TEMP T WHERE CONCAT(T.FEE_YEAR,LPAD(T.FEE_MONTH,2,'0')) IN('201703','201704','201705'))