请看一下这种SQL语句如何优化。

chi742658817 2017-11-29 02:06:12 
SELECT ST_ITEM,

       ST_VER,

       DECODE(ST_CFMMAN, '', 'NO', 'YES') AS ST_CFMMAN,

       DECODE(ST_TRMAN, '', 'NO', 'YES') AS ST_TRMAN,

       DECODE((SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'IQC'),'','——',(SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'IQC')) AS IQC,

       DECODE((SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'ZC-PD'),'','——',(SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'ZC-PD')) AS ZCPD,

       DECODE((SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'SMT-PD'),'','——',(SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'SMT-PD')) AS SMTPD,

       DECODE((SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'SMT-CS'),'','——',(SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'SMT-CS')) AS SMTC,

       DECODE((SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'SMT-SS'),'','——',(SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'SMT-SS')) AS SMTS,

       DECODE((SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'AIM'),'','——',(SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'AIM')) AS AIM,

       DECODE((SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'INSERT'),'','——',(SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'INSERT')) AS INSERTS,

       DECODE((SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'REPAIR'),'','——',(SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'REPAIR')) AS REPAIRS,

       DECODE((SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'VIEWICT'),'','——',(SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'VIEWICT')) AS VIEWICT,

       DECODE((SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'FCMCCHK'),'','——',(SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'FCMCCHK')) AS FCMCCHK,

       DECODE((SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'ASSY'),'','——',(SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'ASSY')) AS ASSY,

       DECODE((SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'FINALCHK'),'','——',(SELECT SUM(STS_TS)FROM STTS WHERE STS_ITEM = ST_ITEM AND STS_VER = ST_VER AND STS_WS = 'FINALCHK')) AS FINALCHK

       FROM STT LEFT JOIN STTS  ON ST_ITEM = STS_ITEM  AND ST_VER = STS_VER

       GROUP BY ST_ITEM, ST_VER, ST_CFMMAN, ST_TRMAN

       ORDER BY ST_ITEM, ST_VER;


中间的DECODE需要sum合计数据，所以在里面查了一遍，AS 又不行，
所以不为空时这样的内查询sql又重复了一遍,
AS又不行，请高手看下怎么能优化一下，短一点也好...
...全文
275 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
碧水幽幽泉 2017-11-29
  • 打赏
  • 举报
 回复
引用 8 楼 chi742658817 的回复:
[quote=引用 7 楼 qq646748739 的回复:]
你的语句太长了，行不通，极大地影响了性能。
做数据库这一行，性能远远比(查询)结果重要。

报了无效数字错误......
额，我不是专做数据库的，是搞开发的，数据库方面的能力，
只限在基本的增删改查，
深入点的优化能力就不行了，想学习也不知道从何学起才能加深这方面的能力，
只能靠慢慢积累....遇到一次问题就记一次解决方式...[/quote]
无效数字，是因为STS_TS中还有包含字符串的情况，这个就需要你自己去排查了。
chi742658817 2017-11-29
  • 打赏
  • 举报
 回复
引用 7 楼 qq646748739 的回复:
你的语句太长了，行不通，极大地影响了性能。 做数据库这一行，性能远远比(查询)结果重要。
报了无效数字错误...... 额，我不是专做数据库的，是搞开发的，数据库方面的能力， 只限在基本的增删改查， 深入点的优化能力就不行了，想学习也不知道从何学起才能加深这方面的能力， 只能靠慢慢积累....遇到一次问题就记一次解决方式...
碧水幽幽泉 2017-11-29
  • 打赏
  • 举报
 回复
你的语句太长了，行不通，极大地影响了性能。
做数据库这一行，性能远远比(查询)结果重要。
碧水幽幽泉 2017-11-29
  • 打赏
  • 举报
 回复
我把DECODE敲成DEOCDE了，你用下面的语句不行？


SELECT ST_ITEM,

        ST_VER,

        DECODE(ST_CFMMAN, '', 'NO', 'YES') AS ST_CFMMAN,

        DECODE(ST_TRMAN, '', 'NO', 'YES') AS ST_TRMAN,

        NVL(SUM(DECODE(TS_WS,'IQC',STS_TS,0)),'——') AS IQC,

        NVL(SUM(DECODE(TS_WS,'ZC-PD',STS_TS,0)),'——') AS ZCPD,

        NVL(SUM(DECODE(TS_WS,'SMT-PD',STS_TS,0)),'——') AS SMTPD,

        NVL(SUM(DECODE(TS_WS,'SMT-CS',STS_TS,0)),'——') AS SMTCS,

        NVL(SUM(DECODE(TS_WS,'SMT-SS',STS_TS,0)),'——') AS SMTSS,

        NVL(SUM(DECODE(TS_WS,'AIM',STS_TS,0)),'——') AS AIM,

        NVL(SUM(DECODE(TS_WS,'INSERT',STS_TS,0)),'——') AS INSERT,

        NVL(SUM(DECODE(TS_WS,'REPAIR',STS_TS,0)),'——') AS REPAIR,

        NVL(SUM(DECODE(TS_WS,'VIEWICT',STS_TS,0)),'——') AS VIEWICT,

        NVL(SUM(DECODE(TS_WS,'FCMCCHK',STS_TS,0)),'——') AS FCMCCHK,

        NVL(SUM(DECODE(TS_WS,'ASSY',STS_TS,0)),'——') AS ASSY,

        NVL(SUM(DECODE(TS_WS,'FINALCHK',STS_TS,0)),'——') AS FINALCHK

   FROM STT  

   LEFT JOIN STTS ON ST_ITEM = STS_ITEM  

    AND ST_VER = STS_VER

  GROUP BY ST_ITEM, ST_VER, ST_CFMMAN, ST_TRMAN

  ORDER BY ST_ITEM, ST_VER;
chi742658817 2017-11-29
  • 打赏
  • 举报
 回复
引用 3 楼 qq646748739 的回复:
ST_CFMMAN和ST_TRMAN可以不用改成CASE WHEN 
		

 SELECT ST_ITEM,

		ST_VER,

		DECODE(ST_CFMMAN, '', 'NO', 'YES') AS ST_CFMMAN,

        DECODE(ST_TRMAN, '', 'NO', 'YES') AS ST_TRMAN,

		NVL(SUM(DEOCDE(TS_WS,'IQC',STS_TS,0)),'——') AS IQC,

		NVL(SUM(DEOCDE(TS_WS,'ZC-PD',STS_TS,0)),'——') AS ZCPD,

		NVL(SUM(DEOCDE(TS_WS,'SMT-PD',STS_TS,0)),'——') AS SMTPD,

		NVL(SUM(DEOCDE(TS_WS,'SMT-CS',STS_TS,0)),'——') AS SMTCS,

		NVL(SUM(DEOCDE(TS_WS,'SMT-SS',STS_TS,0)),'——') AS SMTSS,

		NVL(SUM(DEOCDE(TS_WS,'AIM',STS_TS,0)),'——') AS AIM,

		NVL(SUM(DEOCDE(TS_WS,'INSERT',STS_TS,0)),'——') AS INSERT,

		NVL(SUM(DEOCDE(TS_WS,'REPAIR',STS_TS,0)),'——') AS REPAIR,

		NVL(SUM(DEOCDE(TS_WS,'VIEWICT',STS_TS,0)),'——') AS VIEWICT,

		NVL(SUM(DEOCDE(TS_WS,'FCMCCHK',STS_TS,0)),'——') AS FCMCCHK,

		NVL(SUM(DEOCDE(TS_WS,'ASSY',STS_TS,0)),'——') AS ASSY,

		NVL(SUM(DEOCDE(TS_WS,'FINALCHK',STS_TS,0)),'——') AS FINALCHK

   FROM STT  

   LEFT JOIN STTS ON ST_ITEM = STS_ITEM  

    AND ST_VER = STS_VER

  GROUP BY ST_ITEM, ST_VER, ST_CFMMAN, ST_TRMAN

  ORDER BY ST_ITEM, ST_VER;

原始数据这样的：

修改后是这样：

效果实现了，就是SQL语句太长....
chi742658817 2017-11-29
  • 打赏
  • 举报
 回复
引用 3 楼 qq646748739 的回复:
ST_CFMMAN和ST_TRMAN可以不用改成CASE WHEN 
		
 SELECT ST_ITEM,
		ST_VER,
		DECODE(ST_CFMMAN, '', 'NO', 'YES') AS ST_CFMMAN,
        DECODE(ST_TRMAN, '', 'NO', 'YES') AS ST_TRMAN,
		NVL(SUM(DEOCDE(TS_WS,'IQC',STS_TS,0)),'——') AS IQC,
		NVL(SUM(DEOCDE(TS_WS,'ZC-PD',STS_TS,0)),'——') AS ZCPD,
		NVL(SUM(DEOCDE(TS_WS,'SMT-PD',STS_TS,0)),'——') AS SMTPD,
		NVL(SUM(DEOCDE(TS_WS,'SMT-CS',STS_TS,0)),'——') AS SMTCS,
		NVL(SUM(DEOCDE(TS_WS,'SMT-SS',STS_TS,0)),'——') AS SMTSS,
		NVL(SUM(DEOCDE(TS_WS,'AIM',STS_TS,0)),'——') AS AIM,
		NVL(SUM(DEOCDE(TS_WS,'INSERT',STS_TS,0)),'——') AS INSERT,
		NVL(SUM(DEOCDE(TS_WS,'REPAIR',STS_TS,0)),'——') AS REPAIR,
		NVL(SUM(DEOCDE(TS_WS,'VIEWICT',STS_TS,0)),'——') AS VIEWICT,
		NVL(SUM(DEOCDE(TS_WS,'FCMCCHK',STS_TS,0)),'——') AS FCMCCHK,
		NVL(SUM(DEOCDE(TS_WS,'ASSY',STS_TS,0)),'——') AS ASSY,
		NVL(SUM(DEOCDE(TS_WS,'FINALCHK',STS_TS,0)),'——') AS FINALCHK
   FROM STT  
   LEFT JOIN STTS ON ST_ITEM = STS_ITEM  
    AND ST_VER = STS_VER
  GROUP BY ST_ITEM, ST_VER, ST_CFMMAN, ST_TRMAN
  ORDER BY ST_ITEM, ST_VER;
不行，报DEOCDE标识符无效....
碧水幽幽泉 2017-11-29
  • 打赏
  • 举报
 回复
ST_CFMMAN和ST_TRMAN可以不用改成CASE WHEN 
		

 SELECT ST_ITEM,

		ST_VER,

		DECODE(ST_CFMMAN, '', 'NO', 'YES') AS ST_CFMMAN,

        DECODE(ST_TRMAN, '', 'NO', 'YES') AS ST_TRMAN,

		NVL(SUM(DEOCDE(TS_WS,'IQC',STS_TS,0)),'——') AS IQC,

		NVL(SUM(DEOCDE(TS_WS,'ZC-PD',STS_TS,0)),'——') AS ZCPD,

		NVL(SUM(DEOCDE(TS_WS,'SMT-PD',STS_TS,0)),'——') AS SMTPD,

		NVL(SUM(DEOCDE(TS_WS,'SMT-CS',STS_TS,0)),'——') AS SMTCS,

		NVL(SUM(DEOCDE(TS_WS,'SMT-SS',STS_TS,0)),'——') AS SMTSS,

		NVL(SUM(DEOCDE(TS_WS,'AIM',STS_TS,0)),'——') AS AIM,

		NVL(SUM(DEOCDE(TS_WS,'INSERT',STS_TS,0)),'——') AS INSERT,

		NVL(SUM(DEOCDE(TS_WS,'REPAIR',STS_TS,0)),'——') AS REPAIR,

		NVL(SUM(DEOCDE(TS_WS,'VIEWICT',STS_TS,0)),'——') AS VIEWICT,

		NVL(SUM(DEOCDE(TS_WS,'FCMCCHK',STS_TS,0)),'——') AS FCMCCHK,

		NVL(SUM(DEOCDE(TS_WS,'ASSY',STS_TS,0)),'——') AS ASSY,

		NVL(SUM(DEOCDE(TS_WS,'FINALCHK',STS_TS,0)),'——') AS FINALCHK

   FROM STT  

   LEFT JOIN STTS ON ST_ITEM = STS_ITEM  

    AND ST_VER = STS_VER

  GROUP BY ST_ITEM, ST_VER, ST_CFMMAN, ST_TRMAN

  ORDER BY ST_ITEM, ST_VER;

碧水幽幽泉 2017-11-29
  • 打赏
  • 举报
 回复
去掉AS FINALCHK, 后面的逗号
		

 SELECT ST_ITEM,

		ST_VER,

		CASE WHEN ST_CFMMAN IS NULL THEN 'NO' ELSE 'YES' END AS ST_CFMMAN,

		CASE WHEN ST_TRMAN IS NULL THEN 'NO' ELSE 'YES' END AS ST_TRMAN,

		NVL(SUM(DEOCDE(TS_WS,'IQC',STS_TS,0)),'——') AS IQC,

		NVL(SUM(DEOCDE(TS_WS,'ZC-PD',STS_TS,0)),'——') AS ZCPD,

		NVL(SUM(DEOCDE(TS_WS,'SMT-PD',STS_TS,0)),'——') AS SMTPD,

		NVL(SUM(DEOCDE(TS_WS,'SMT-CS',STS_TS,0)),'——') AS SMTCS,

		NVL(SUM(DEOCDE(TS_WS,'SMT-SS',STS_TS,0)),'——') AS SMTSS,

		NVL(SUM(DEOCDE(TS_WS,'AIM',STS_TS,0)),'——') AS AIM,

		NVL(SUM(DEOCDE(TS_WS,'INSERT',STS_TS,0)),'——') AS INSERT,

		NVL(SUM(DEOCDE(TS_WS,'REPAIR',STS_TS,0)),'——') AS REPAIR,

		NVL(SUM(DEOCDE(TS_WS,'VIEWICT',STS_TS,0)),'——') AS VIEWICT,

		NVL(SUM(DEOCDE(TS_WS,'FCMCCHK',STS_TS,0)),'——') AS FCMCCHK,

		NVL(SUM(DEOCDE(TS_WS,'ASSY',STS_TS,0)),'——') AS ASSY,

		NVL(SUM(DEOCDE(TS_WS,'FINALCHK',STS_TS,0)),'——') AS FINALCHK

   FROM STT  

   LEFT JOIN STTS ON ST_ITEM = STS_ITEM  

    AND ST_VER = STS_VER

  GROUP BY ST_ITEM, ST_VER, ST_CFMMAN, ST_TRMAN

  ORDER BY ST_ITEM, ST_VER;

碧水幽幽泉 2017-11-29
  • 打赏
  • 举报
 回复

试试这个：
		

 SELECT ST_ITEM,

		ST_VER,

		CASE WHEN ST_CFMMAN IS NULL THEN 'NO' ELSE 'YES' END AS ST_CFMMAN,

		CASE WHEN ST_TRMAN IS NULL THEN 'NO' ELSE 'YES' END AS ST_TRMAN,

		NVL(SUM(DEOCDE(TS_WS,'IQC',STS_TS,0)),'——') AS IQC,

		NVL(SUM(DEOCDE(TS_WS,'ZC-PD',STS_TS,0)),'——') AS ZCPD,

		NVL(SUM(DEOCDE(TS_WS,'SMT-PD',STS_TS,0)),'——') AS SMTPD,

		NVL(SUM(DEOCDE(TS_WS,'SMT-CS',STS_TS,0)),'——') AS SMTCS,

		NVL(SUM(DEOCDE(TS_WS,'SMT-SS',STS_TS,0)),'——') AS SMTSS,

		NVL(SUM(DEOCDE(TS_WS,'AIM',STS_TS,0)),'——') AS AIM,

		NVL(SUM(DEOCDE(TS_WS,'INSERT',STS_TS,0)),'——') AS INSERT,

		NVL(SUM(DEOCDE(TS_WS,'REPAIR',STS_TS,0)),'——') AS REPAIR,

		NVL(SUM(DEOCDE(TS_WS,'VIEWICT',STS_TS,0)),'——') AS VIEWICT,

		NVL(SUM(DEOCDE(TS_WS,'FCMCCHK',STS_TS,0)),'——') AS FCMCCHK,

		NVL(SUM(DEOCDE(TS_WS,'ASSY',STS_TS,0)),'——') AS ASSY,

		NVL(SUM(DEOCDE(TS_WS,'FINALCHK',STS_TS,0)),'——') AS FINALCHK,

   FROM STT  

   LEFT JOIN STTS ON ST_ITEM = STS_ITEM  

    AND ST_VER = STS_VER

  GROUP BY ST_ITEM, ST_VER, ST_CFMMAN, ST_TRMAN

  ORDER BY ST_ITEM, ST_VER;
慢SQL语句优化
对慢SQL语句优化一般可以按下面几步思路：开启慢查询日志，设置超过几秒为慢SQL语句，抓取慢SQL语句；通过explain查看执行计划，对慢SQL语句分析；创建索引并调整语句，再查看执行计划，对比调优结果。 　　参数 slow_query_log：表示是否开启慢查询日志。语句“set global slow_query_log=on”临时开启慢查询日志，如果想关闭慢查询日志只需要执行“set global slow_query_log=off ”即可。 参数slow_query_log_..
如何对 SQL 语句进行优化
当我们在设计一个项目时，开发阶段的业务数据量相对较少，一些 SQL 语句的执行效率对程序运行效率的影响不太明显，所以对于开发或运维人员来说不会注重对 SQL 语句的优化。但是，随着时间的积累，业务数据量会越来越多，SQL 的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。 本篇章主要从以下几个方面对 SQL 语句的优化进行介绍： 插入数据优化 主键优化 order by 优化 group by 优化 limit 优化 count 优化 update 优化
Oracle SQL语句之常见优化方法总结
下面列举一些工作中常常会碰到的Oracle的SQL语句优化方法： 1、SQL语句尽量用大写的；  因为oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。 2、使用表的别名：   当在SQL语句中连接多个表时, 尽量使用表的别名并把别名前缀于每个列上。这样一来, 就可以减少解析的时间并减少那些由列歧义引起的语法错误。 3、选择最有效率的表名顺序(只在基于规则的优化器(RB
SQL语句的优化方法
SQL语句的优化方法
sql语句优化的13种方法
原文链接：http://blog.csdn.net/u012942818/article/details/53969972 1，什么是“执行计划”？ 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的，比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录，那查询优化器会选择“索引查找”方式，如果该表进行了归档，当前只剩下500
Oracle

17,082

社区成员

55,236

社区内容

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

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章