帮小弟我优化上这个语句，多谢

帮我优化下这个语句，谢谢

SQL code

alter view YC_GBJEQR_Rep_Vasselect a.ID           ID, a.BH           BH, a.CH           CH, a.CL_MCGG      CL_MCGG, a.DYJZ         DYJZ, a.LFM          LFM, a.RQSJ1        RQSJ1, a.RQSJ2        RQSJ2, a.SFSH         SFSH, a.SHRY         SHRY, b.YCGYS        YCGYS, (select top 1 c.JLDW [JLDW] from YC_CGHT_MX_T c where c.CL_MCGG = a.CL_MCGG and c.YC_CGHT_ID = a.DWMC_GY_ID and a.RQSJ2 >= c.SXRQ order by SXRQ desc) JLDW, (select top 1 c.YCJG [DJ] from YC_CGHT_MX_T c where c.CL_MCGG = a.CL_MCGG and c.YC_CGHT_ID = a.DWMC_GY_ID and a.RQSJ2 >= c.SXRQ order by SXRQ desc) DJ,--采购合同原材计价方式，方、吨，默认为吨 --以计量单位为判断条件，计算金额(   case when (select top 1 c.JLDW [JLDW] from YC_CGHT_MX_T c where c.CL_MCGG = a.CL_MCGG and c.YC_CGHT_ID = a.DWMC_GY_ID and a.RQSJ2 >= c.SXRQ order by SXRQ desc) = '方' then       dbo.StrToNumeric2(a.LFM)*dbo.StrToNumeric2((select top 1 c.YCJG [DJ] from YC_CGHT_MX_T c where c.CL_MCGG = a.CL_MCGG and c.YC_CGHT_ID = a.DWMC_GY_ID and a.RQSJ2 >= c.SXRQ order by SXRQ desc))    else       dbo.StrToNumeric2(a.DYJZ)*dbo.StrToNumeric2((select top 1 c.YCJG [DJ] from YC_CGHT_MX_T c where c.CL_MCGG = a.CL_MCGG and c.YC_CGHT_ID = a.DWMC_GY_ID and a.RQSJ2 >= c.SXRQ order by SXRQ desc))   end  ) JE from YC_JLGB_T aleft join YC_CGHT_T b on a.DWMC_GY_ID = b.ID

涉及三张表YC_JLGB_T 、 YC_CGHT_T 、 YC_CGHT_MX_T  

因为YC_CGHT_MX_T 存在多条单价的缘故，所以不能简单实用LEFT JOIN ，我需要根据不同时间段的价格计算金额

再次感谢！！！！

[解决办法]
看着乱。。帮顶！！！
[解决办法]

SQL code

/*select top 1 用的太多了你的意思无非就是取最大时间对应的值这还是有比较好的算法的，论坛里有，搜索下吧*/
[解决办法]
以下是我帮你的优化，你测试下
SQL code
alter view YC_GBJEQR_Rep_Vasselect a.ID           ID, a.BH           BH, a.CH           CH, a.CL_MCGG      CL_MCGG, a.DYJZ         DYJZ, a.LFM          LFM, a.RQSJ1        RQSJ1, a.RQSJ2        RQSJ2, a.SFSH         SFSH, a.SHRY         SHRY, a.DWMC_GY_ID    DWMC_GY_IDinto #tmpfrom YC_JLGB_TALTER TABLE #tmp ADD [JLDW] VARCHAR(30)ALTER TABLE #tmp ADD [DJ] VARCHAR(30)update #tmp set [JLDW]=t.[JLDW],[DJ]=t.[DJ]from #tmp dinner join(    select rank() over(order by [SXRQ] DESC) rn1, row_number() over(order by [SXRQ] DESC) rn2,c.JLDW [JLDW],c.YCJG [DJ],a.DWMC_GY_ID,a.CL_MCGG     from YC_JLGB_T a    inner join YC_CGHT_MX_T c     on c.CL_MCGG = a.CL_MCGG and c.YC_CGHT_ID = a.DWMC_GY_ID and a.RQSJ2 >= c.SXRQ ) ton t.rn1=t.rn2 and d.CL_MCGG = t.CL_MCGG and d.DWMC_GY_ID = t.DWMC_GY_IDselect  a.ID           ID, a.BH           BH, a.CH           CH, a.CL_MCGG      CL_MCGG, a.DYJZ         DYJZ, a.LFM          LFM, a.RQSJ1        RQSJ1, a.RQSJ2        RQSJ2, a.SFSH         SFSH, a.SHRY         SHRY, b.YCGYS        YCGYS, a.[JLDW]        JLDW, a.[DJ]            DJ,(   case when a.[JLDW] = '方' then       dbo.StrToNumeric2(a.LFM)*dbo.StrToNumeric2(a.[DJ]))    else       dbo.StrToNumeric2(a.DYJZ)*dbo.StrToNumeric2((a.[DJ]))   end  ) JEfrom #tmp aleft join YC_CGHT_T b on a.DWMC_GY_ID = b.IDdrop table #tmp 

 

查看更多 下一篇