While 循环创建重复记录 [英] While loop creating duplicate records

问题描述

我有一个非常简单的查询，它需要两个日期(开始和结束日期，例如: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 循环创建重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！