sql - 过去 26 周及其各自的周数

Question

如果我今天(2012 年 8 月 21 日)运行查询，我想得到这个结果集:



[...]



为了得到这个，我正在使用以下内容，但它似乎过于复杂。这可以简化吗？

;WITH Numbers_cte([number]) 
        AS 
        ( --return the numbers from 1 to 182 i.e 26*7
        SELECT DISTINCT number 
        FROM Master..spt_values 
        WHERE number BETWEEN 1 AND 182
        )
,MultipleSeven_cte([number], [multiple])
        AS 
        ( --divide the number series by 7 and return integers 
        SELECT 
            [number]
            ,[multiple] = (([number]-1) / 7)+1
        FROM Numbers_cte
        ) 
,Today_cte([Today])
        AS 
        ( --return the last date in the table or use GETDATE for this example
        SELECT [Today]=CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE()-1,112))
        ) 
,EquivDates_cte([multiple],[number],[Today], [EquivDates])
        AS
        ( 
        SELECT 
             x.multiple
            ,x.number
            ,y.Today 
            ,[EquivDates] = DATEADD(DAY,-(182-x.number),y.[Today]) 
        FROM MultipleSeven_cte x, Today_cte y
        )                   
SELECT 
        multiple
        ,number
        ,[EquivDates] 
FROM EquivDates_cte

Answer 1

你可以这样做:

select (number/7)+1 as multiple ,
        number+1 as number,
        dateadd(dd,-(182-number),GETDATE()) as EquivDates
from    master..spt_values 
where   type='P'
and     number<182

SQL Fiddle Demo