求论坛大佬优化一下sql

迟刀 2018-03-13 02:40:55


...全文
579 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
jdsnhan 2018-03-16
  • 打赏
  • 举报
回复
引用 4 楼 jdsnhan 的回复:
每个结构说明,没有执行计划,咋让大家帮你啊
按F5,会出执行计划。
迟刀 2018-03-15
  • 打赏
  • 举报
回复
引用 4 楼 jdsnhan 的回复:
每个结构说明,没有执行计划,咋让大家帮你啊
不好意思,刚入职的小白。。。。
迟刀 2018-03-15
  • 打赏
  • 举报
回复
这就把表结构和要求发上来
jdsnhan 2018-03-15
  • 打赏
  • 举报
回复
每个结构说明,没有执行计划,咋让大家帮你啊
迟刀 2018-03-13
  • 打赏
  • 举报
回复
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'))
迟刀 2018-03-13
  • 打赏
  • 举报
回复
引用 1 楼 baidu_36457652 的回复:
把中间的with 表放到临时表试试
好的,我试试
  • 打赏
  • 举报
回复
把中间的with 表放到临时表试试

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧