诸位高人，看一下这SQL还能不能再优化一下
日期：2014-05-17 浏览次数：20498 次
各位高人，看一下这SQL还能不能再优化一下
用于计算当年周次
水平有限，不知是否可以进一步优化，还请各路高人指点
------解决方案--------------------
函数用的太多了，没必要用那么多吧？漫山遍野的都是红色。
------解决方案--------------------
用于计算当年周次
select number
,right(convert(varchar,case when year(dateadd(dd,(datepart(dw,dateadd(ww,(datepart(ww,cast(year(getdate()) as varchar)+'-1-1')-1)*-1, cast(year(getdate()) as varchar)+'-1-1'))-1)*-1
,dateadd(ww, (datepart(ww, cast(year(getdate()) as varchar)+'-1-1' )-number)*-1, cast(year(getdate()) as varchar)+'-1-1'))) < year(getdate())
then cast(year(getdate()) as varchar)+'-1-1'
else dateadd(dd,(datepart(dw,dateadd(ww,(datepart(ww,cast(year(getdate()) as varchar)+'-1-1')-1)*-1, cast(year(getdate()) as varchar)+'-1-1'))-1)*-1
,dateadd(ww, (datepart(ww, cast(year(getdate()) as varchar)+'-1-1' )-number)*-1, cast(year(getdate()) as varchar)+'-1-1'))
end,102),5) + '~' +
right(convert(varchar,case when year(dateadd(dd,(datepart(dw,dateadd(ww,(datepart(ww,cast(year(getdate()) as varchar)+'-1-1')-1)*-1, cast(year(getdate()) as varchar)+'-1-1'))-1)*-1
,dateadd(ww, (datepart(ww, cast(year(getdate()) as varchar)+'-1-1' )-number)*-1, cast(year(getdate()) as varchar)+'-1-1'))+6) > year(getdate())
then cast(year(getdate()) as varchar)+'-12-31'
else dateadd(dd,(datepart(dw,dateadd(ww,(datepart(ww,cast(year(getdate()) as varchar)+'-1-1')-1)*-1, cast(year(getdate()) as varchar)+'-1-1'))-1)*-1
,dateadd(ww, (datepart(ww, cast(year(getdate()) as varchar)+'-1-1' )-number)*-1, cast(year(getdate()) as varchar)+'-1-1'))+6
end,102),5) as date_between
from master..spt_values
where type='p'
and number < datepart(ww,cast(year(getdate()) as varchar)+'-12-31')+1
and number > 0
水平有限，不知是否可以进一步优化，还请各路高人指点
优化
sql
------解决方案--------------------
函数用的太多了，没必要用那么多吧？漫山遍野的都是红色。
------解决方案--------------------
WITH tt
AS ( SELECT DATEPART(wk,
DATEADD(dd, m.number,
DATEADD(yy,
DATEDIFF(yy, 0, GETDATE()),
&
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
