mysql - 多个日期添加 - 是否可以循环？

Question

我是 SQL 的新手，我正在尝试创建一个语句，将一周添加到日期，并在周数下列出所述日期。

输出工作正常:

fin_year | MondayWeek1Date |     WK1    |     WK2    | 
2016     | 2015-07-27      | 2015-07-27 | 2015-08-03 |

代码:

SELECT  fin_year, MondayWeek1Date,
             DATEADD(WK,0,MondayWeek1Date) AS WK1,
             DATEADD(WK,1,MondayWeek1Date) AS WK2,
             DATEADD(WK,2,MondayWeek1Date) AS WK3,
             DATEADD(WK,3,MondayWeek1Date) AS WK4,
             DATEADD(WK,4,MondayWeek1Date) AS WK5,
             DATEADD(WK,5,MondayWeek1Date) AS WK6,
             DATEADD(WK,6,MondayWeek1Date) AS WK7,
             DATEADD(WK,7,MondayWeek1Date) AS WK8
    FROM         server.financeYearStartDate
    WHERE     (fin_year = @fin_period)

但是有没有办法让我将 DATEADD 循环到某个点，这样我就不必有 52/53 行的日期添加？

谢谢!

尼尔

Answer 1

如果这是我猜想的 SQL Server，您可以使用递归 cte 添加周数，然后将其转换为列。示例:

-- temp table to use in the example
    create table #financeYearStartDate
    (fin_year integer, MondayWeek1Date date)
    insert into #financeYearStartDate
    Values
    (2016,'2016-01-04'),
    (2017,'2017-01-02')

-- 

;with cte
as 
(select fin_year, cast('MondayWeek1Date' as varchar(15)) as weeknumber, MondayWeek1Date weekdate, 0 wn from #financeYearStartDate
union all
select fin_year, 'W' + cast(wn+1 as varchar(14)) weeknumber, DATEADD(WK,1,weekdate), wn+1 wn from cte where wn<53
)

SELECT fin_year, 
[MondayWeek1Date], 
[W1], [W2], [W3], [W4], [W5], [W6], [W7], [W8], [W9], [W10],
[W11], [W12], [W13], [W14], [W15], [W16], [W17], [W18], [W19], [W20], 
[W21], [W22], [W23], [W24], [W25], [W26], [W27], [W28], [W29], [W30], 
[W31], [W32], [W33], [W34], [W35], [W36], [W37], [W38], [W39], [W40], 
[W41], [W42], [W43], [W44], [W45], [W46], [W47], [W48], [W49], [W50], 
[W51], [W52]
FROM
(SELECT fin_year, weeknumber, weekdate 
    FROM cte) AS SourceTable
PIVOT
(
Max(weekdate)
FOR weeknumber IN ([MondayWeek1Date], 
[W1], [W2], [W3], [W4], [W5], [W6], [W7], [W8], [W9], [W10],
[W11], [W12], [W13], [W14], [W15], [W16], [W17], [W18], [W19], [W20], 
[W21], [W22], [W23], [W24], [W25], [W26], [W27], [W28], [W29], [W30], 
[W31], [W32], [W33], [W34], [W35], [W36], [W37], [W38], [W39], [W40], 
[W41], [W42], [W43], [W44], [W45], [W46], [W47], [W48], [W49], [W50], 
[W51], [W52])
) AS PivotTable