問題描述

我有一個非常簡單的查詢，它需要兩個日期(開始和結束日期，例如:01/01/2018-01/31/2018)，然后取出開始和結束日期的周數(例如:1 和5).我使用 while 循環在一張表中輸入所有周數(例如:1、2、3、4、5).我的 while 循環正在創建重復記錄，例如所有值的多個記錄.我不確定是什么問題.有人可以建議我可能做錯了什么嗎?

I have a very simple query that takes two dates (start and end date, ex: 01/01/2018-01/31/2018) and then take out the week number of start and end date (ex: 1 and 5). I use while loop in order to enter all week numbers in one table (ex: 1,2,3,4,5). My while loop is creating duplicate records, like multiple records for all values. I am not sure what the issue is. Can someone suggest what I am might be doing wrong?

代碼:

DECLARE @StartDate datetime = '01/01/2018'
    ,@EndDate datetime = '01/31/2018'
DECLARE @StartDateWeek INT  = DATEPART(WK,@StartDate)
    ,@EndDateWeek INT   = DATEPART(WK,@EndDate)
SELECT @StartDateWeek,@EndDateWeek

DECLARE @DateRangeWeekList TABLE(
Date DATE
,WeekNumber INT)

INSERT INTO @DateRangeWeekList
SELECT  Date = @StartDate
        ,WeekNumber = @StartDateWeek
select * from @DateRangeWeekList


WHILE (SELECT MAX(WeekNumber) FROM @DateRangeWeekList) < (SELECT     @EndDateWeek)
BEGIN
INSERT INTO @DateRangeWeekList
SELECT  Date = null
        ,WeekNumber = WeekNumber +1
FROM @DateRangeWeekList
END

select * from @DateRangeWeekList

結果:

Date        WeekNumber
2018-01-01  1
NULL        2
NULL        2
NULL        3
NULL        2
NULL        3
NULL        3
NULL        4
NULL        2 and more (total 18 records)

預期結果

Date       WeekNumber
2018-01-01 1
NULL       2
NULL       3
NULL       4
NULL       5

推薦答案

遞歸 CTE 在這里比 WHILE 循環要好得多:

A recursive CTE would be far better here than a WHILE loop:

DECLARE @StartDate datetime = '01/01/2018',
        @EndDate datetime = '01/31/2018';

WITH WeekNumbers AS(
    SELECT CONVERT(date,@StartDate) AS [Date],
           1 AS WeekNumber
    UNION ALL
    SELECT NULL,
           WeekNumber + 1
    FROM WeekNumbers
    WHERE WeekNumber <= DATEDIFF(WEEK, @StartDate, @EndDate))
SELECT *
FROM WeekNumbers;

不過，您也可以考慮使用日歷表.有很多選擇供您選擇.

You could also, however, consider using a Calendar table. There are lots of options out there for you.

這篇關于While 循環創建重復記錄的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！