日期:2014-05-17  浏览次数:20608 次

帮我优化下这个语句,谢谢
SQL code


alter view YC_GBJEQR_Rep_V
as
select
 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 a
left 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_V
as
select
 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_ID
into #tmp
from YC_JLGB_T

ALTER TABLE #tmp ADD [JLDW] VARCHAR(30)
ALTER TABLE #tmp ADD [DJ] VARCHAR(30)

update #tmp set [JLDW]=t.[JLDW],[DJ]=t.[DJ]
from #tmp d
inner 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 
) t
on t.rn1=t.rn2 and d.CL_MCGG = t.CL_MCGG and d.DWMC_GY_ID = t.DWMC_GY_ID

select 
 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 
 ) JE
from #tmp a
left join YC_CGHT_T b 
on a.DWMC_GY_ID = b.ID

drop table #tmp