怎么优化这个SQL语句

如何优化这个SQL语句

SQL code

select a.RWDBH [RWDBH],a.QDDJ [QDDJ],CL_MCGG,(case SFSJ  when '是' then '是' else '否' end) SFSJ from SC_FHD_T aleft join SY_SGPHB_T b on a.RWDBH = b.RWDBHleft join SY_PHB_YH_LZH_V c on b.ID = c.SY_SGPHB_IDwhere 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_Vasselect  SY_SGPHB_ID        SY_SGPHB_ID, PHBBH_YH            PHBBH_YH, PHB_SYL            PHB_YL, '水[水]'            CL_MCGGfrom SY_SGPHB_PHBJL_Tunion --水泥select  SY_SGPHB_ID        SY_SGPHB_ID, PHBBH_YH            PHBBH_YH, PHB_SNYL            PHB_YL, SNMCGG                CL_MCGGfrom SY_SGPHB_PHBJL_Tunion --细集料1select  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_MCGGfrom SY_SGPHB_PHBJL_Tunion --细集料2select  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_MCGGfrom SY_SGPHB_PHBJL_Tunion --粗集料1select  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_MCGGfrom SY_SGPHB_PHBJL_Tunion --粗集料2select  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_MCGGfrom SY_SGPHB_PHBJL_Tunion --掺合料1select  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_MCGGfrom SY_SGPHB_PHBJL_Tunion --掺合料2select  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_MCGGfrom SY_SGPHB_PHBJL_Tunion --掺合料3select  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_MCGGfrom SY_SGPHB_PHBJL_Tunion --外加剂1select  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_MCGGfrom SY_SGPHB_PHBJL_Tunion --外加剂2select  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_MCGGfrom SY_SGPHB_PHBJL_Tunion --外加剂3select  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_MCGGfrom 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-……

查看更多 下一篇