首页新闻找找看学习计划

如何优化CASE WHEN中的重复SQL?

0
[已解决问题] 解决于 2018-06-02 14:42

--建安
SELECT
---------------我想优化这里的SQL--------------------
---------------因为下面的这两句SQL是完全重复的-------
---------------看起来很难受-------------------------

CASE
when (select top 1 id
from report_da8d2d40c0a801f500f6178dadb40173 r2,report_project_step b1,report_project_step b2
where r2.businessid=b1.fid and b1.project=b2.project and rpt_uuid.businessid=b2.fid
and r2.alias=rpt_uuid.alias and r2.copy_alias=rpt_uuid.copy_alias) is null
THEN (select top 1 id
from report_da8d2d40c0a801f500f6178dadb40173 r2,report_project_step b1,report_project_step b2
where r2.businessid=b1.fid and b1.project=b2.project and rpt_uuid.businessid=b2.fid
and r2.alias=rpt_uuid.alias and r2.copy_alias=rpt_uuid.copy_alias)
-----------------------------------
ELSE rpt_uuid.ID
END AS "ID"
,ISNULL(
(select a.fid from t_project_survey a where a.fid=rpt_project_step.project)
,(select r.fid from t_project r where r.fid=rpt_project_step.project)
) as "项目ID"
,10 AS "类型"
,335 AS "状态"
,rpt_uuid.f4 as "建设内容"
,rpt_uuid.f5 as "单位"
,rpt_uuid.f6 as "建筑形式"
,rpt_uuid.f1 as "规模数量"
,rpt_uuid.f2 as "单价"
,rpt_uuid.f21 AS "备注"
,ROW_NUMBER() over(order by rpt_uuid.f1) as "序号"

FROM report_e3b88af8c0a801f50029bf4949f737de AS rpt_uuid
left join report_project_step as rpt_project_step on rpt_uuid.businessId=rpt_project_step.fid
WHERE
(
rpt_uuid.ALIAS = 'A'
OR rpt_uuid.copy_alias='A'
)
AND rpt_uuid.f4 <>''
AND rpt_project_step.project='4affaa2d44727a9f01448babf6b60d78'

renguanyu的主页 renguanyu | 小虾三级 | 园豆:525
提问于:2018-04-22 21:06
< >
分享
最佳答案
0

case when语句可以使用函数来代替,或者使用join 来代替,

奖励园豆:5
悦光阴 | 老鸟四级 |园豆:2239 | 2018-05-15 09:49
其他回答(1)
0

不要因为看起来很难受 去优化sql
你要注意的是sql的性能

、熙和 | 园豆:1486 (小虾三级) | 2018-06-02 17:23

其实最佳答案并不是最佳

只是当时没谁答,然后我就随便选了一个

这个问题最好的解决方案就是把非常费时的SQL给缓存到一张表里,然后直接去查询缓存表

创建缓存表的时候记得给连接字段加索引,我这个SQL当时之所以慢就是因为有一些字段没有索引

加完索引后就非常快了

支持(0) 反对(0) renguanyu | 园豆:525 (小虾三级) | 2018-06-08 09:18
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册