日期:2014-05-18  浏览次数:20910 次

如何优化这个SQL语句
SQL code


select a.RWDBH [RWDBH],a.QDDJ [QDDJ],CL_MCGG,(case SFSJ  when '是' then '是' else '否' end) SFSJ from SC_FHD_T a
left join SY_SGPHB_T b on a.RWDBH = b.RWDBH
left join SY_PHB_YH_LZH_V c on b.ID = c.SY_SGPHB_ID
where isnull(c.PHB_YL,'') <> '' and isnull(c.CL_MCGG,'') <> '' 
and a.RQSJ >='2011-11-03 08:00:00'  and a.RQSJ <='2011-11-04 07:59:59'  
group by a.RWDBH,a.QDDJ,(case SFSJ  when '是' then '是' else '否' end),CL_MCGG




有一个视图 SY_PHB_YH_LZH_V 是行转列视图,大概有3W条数据,如果按照这样执行,运行时间超过10秒。
我想请教一下当如何优化?

附带SY_PHB_YH_LZH_V视图代码
SQL code

Create view SY_PHB_YH_LZH_V
as
select 
 SY_SGPHB_ID        SY_SGPHB_ID,
 PHBBH_YH            PHBBH_YH,
 PHB_SYL            PHB_YL,
 '水[水]'            CL_MCGG
from SY_SGPHB_PHBJL_T
union 
--水泥
select 
 SY_SGPHB_ID        SY_SGPHB_ID,
 PHBBH_YH            PHBBH_YH,
 PHB_SNYL            PHB_YL,
 SNMCGG                CL_MCGG
from SY_SGPHB_PHBJL_T
union 
--细集料1
select 
 SY_SGPHB_ID        SY_SGPHB_ID,
 PHBBH_YH            PHBBH_YH,
 PHB_XJLYL_1        PHB_YL,
 (case XJLMCGG_1 when '细集料1' then '' else XJLMCGG_1 end)                CL_MCGG
from SY_SGPHB_PHBJL_T
union 
--细集料2
select 
 SY_SGPHB_ID        SY_SGPHB_ID,
 PHBBH_YH            PHBBH_YH,
 PHB_XJLYL_2        PHB_YL,
 (case XJLMCGG_2 when '细集料2' then '' else XJLMCGG_2 end)                CL_MCGG
from SY_SGPHB_PHBJL_T
union 
--粗集料1
select 
 SY_SGPHB_ID        SY_SGPHB_ID,
 PHBBH_YH            PHBBH_YH,
 PHB_CJLYL_1        PHB_YL,
 (case CJLMCGG_1 when '粗集料1' then '' else CJLMCGG_1 end)                CL_MCGG
from SY_SGPHB_PHBJL_T
union 
--粗集料2
select 
 SY_SGPHB_ID        SY_SGPHB_ID,
 PHBBH_YH            PHBBH_YH,
 PHB_CJLYL_2        PHB_YL,
(case CJLMCGG_2 when '粗集料2' then '' else CJLMCGG_2 end)                CL_MCGG
from SY_SGPHB_PHBJL_T
union 
--掺合料1
select 
 SY_SGPHB_ID        SY_SGPHB_ID,
 PHBBH_YH            PHBBH_YH,
 PHB_CHLYL_1        PHB_YL,
(case CHLMCGG_1 when '掺合料1' then '' else CJLMCGG_1 end)                CL_MCGG
from SY_SGPHB_PHBJL_T
union 
--掺合料2
select 
 SY_SGPHB_ID        SY_SGPHB_ID,
 PHBBH_YH            PHBBH_YH,
 PHB_CHLYL_2        PHB_YL,
(case CHLMCGG_2 when '掺合料2' then '' else CHLMCGG_2 end)                CL_MCGG
from SY_SGPHB_PHBJL_T
union 
--掺合料3
select 
 SY_SGPHB_ID        SY_SGPHB_ID,
 PHBBH_YH            PHBBH_YH,
 PHB_CHLYL_3        PHB_YL,
(case CHLMCGG_3 when '掺合料3' then '' else CHLMCGG_3 end)                CL_MCGG
from SY_SGPHB_PHBJL_T
union 
--外加剂1
select 
 SY_SGPHB_ID        SY_SGPHB_ID,
 PHBBH_YH            PHBBH_YH,
 PHB_WJJYL_1        PHB_YL,
(case WJJMCGG_1 when '外加剂1' then '' else WJJMCGG_1 end)                CL_MCGG
from SY_SGPHB_PHBJL_T
union 
--外加剂2
select 
 SY_SGPHB_ID        SY_SGPHB_ID,
 PHBBH_YH            PHBBH_YH,
 PHB_WJJYL_2        PHB_YL,
(case WJJMCGG_2 when '外加剂2' then '' else WJJMCGG_2 end)                CL_MCGG
from SY_SGPHB_PHBJL_T
union 
--外加剂3
select 
 SY_SGPHB_ID        SY_SGPHB_ID,
 PHBBH_YH            PHBBH_YH,
 PHB_WJJYL_3        PHB_YL,
(case WJJMCGG_3 when '外加剂3' then '' else WJJMCGG_3 end)                CL_MCGG
from SY_SGPHB_PHBJL_T



------解决方案--------------------
探讨

SQL code


select a.RWDBH,a.QDDJ,(case SFSJ when '是' then '是' else '否' end) SFSJ from
(select distinct RWDBH,QDDJ,(case SFSJ when '是' then '是' else '否' end) SFSJ from SC_FHD_T where RQSJ >='2011-……