在SQL Server 2008中使用While循环迭代日期范围并进行插入

Question

在SQL Server 2008中使用While循环迭代日期范围并进行插入

sqlsql-server-2008

23

我有一个包含几列的表格，其中一列是时间戳列。但是目前在这个表格中，并不是每天都有记录。也就是说，存在1月1日和1月2日的记录，但是在时间戳字段中没有1月3日或1月4日的记录，然而1月5日、1月6日等日期有记录。基本上缺少周末和其他随机日期的记录。

我正在尝试编写一个脚本，从开始日期到结束日期（无论我选择哪个日期范围），扫描这个表格，并遍历该日期范围。如果在该日期范围内不存在任何日期的记录，则在时间戳字段中插入具体日期的新记录，但是其余字段为空/NULL数据。

这是我目前拥有的伪代码，我认为这是正确的方法：

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME

SET @StartDate = '2015-01-01'
SET @EndDate = GETDATE()
SET @CurrentDate = @StartDate

WHILE (@CurrentDate < @EndDate)
BEGIN
    SELECT * FROM myTable WHERE myTable.Timestamp = "@CurrentDate"
    IF @@ROWCOUNT < 1
        print @CurrentDate
        /*insert a new row query here*/

    SET @CurrentDate = convert(varchar(30), dateadd(day,1, @CurrentDate), 101); /*increment current date*/
END

这是SQLFiddle的链接 - http://sqlfiddle.com/#!6/06c73/1

我正在编写我的第一个 SQL Server Management Studio 2008 脚本，我认为这可能适用于中级用户。我是一名 PHP/MySQL 开发者，对这些技术非常熟悉，但是我对 SQL 和 VBScript 还很陌生。我理解编程概念和逻辑，但这似乎与我所习惯的迥然不同。

非常感谢您提前给予的所有帮助和见解！

- Armin

2个回答

1

我使用的是一种简单的解决方案，就像GarethD一样没有太多经验。

假设您的表格确实具有以下数据

DECLARE @t TABLE(Dt Date,Name varchar(10))
INSERT INTO @t VALUES
('2021-05-18','hi'),('2021-05-20','heloo'),('2021-05-25','welocme'),('2021-05-27','goto')



DECLARE @startDate DATE, @endDate DATE
Set @startDate = '2021-05-18'
set @endDate = GETDATE()

;WITH Calender AS (
    SELECT @startDate AS YourDate
    UNION ALL
    SELECT DATEADD(day,1,YourDate) FROM Calender
    WHERE DATEADD(day,1,YourDate) <= @endDate
)
INSERT INTO @t SELECT
    Dt = YourDate,Name = DATENAME (WEEKDAY,YourDate)

FROM Calender c
LEFT JOIN @t t 
ON t.Dt = c.YourDate
WHERE t.dt IS NULL
option (maxrecursion 0)

SELECT * FROM @t ORDER BY dt

- Ali

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- GarethD · Accepted Answer

SQL是一种基于集合的语言，循环应该是最后的选择。因此，基于集合的方法是先生成所需的所有日期并一次性插入，而不是一次一个地循环插入。Aaron Bertrand撰写了一篇关于如何在不使用循环的情况下生成集合或序列的系列文章，以下是其中部分内容:

第3部分特别关注日期处理。

假设您没有一个可用的日历表，您可以使用堆叠的CTE方法生成在开始日期和结束日期之间的日期列表。

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2)
SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @StartDate)
FROM N3;

我在此处省略了具体的实现细节，因为这些内容已经在链接的文章中涉及。简而言之，它始于一个硬编码的10行表格，然后将该表格与自身连接以获取100行（10 x 10），然后将该100行表格再次连接自身以获取10,000行（我到此为止，但如果需要更多行，则可以添加更多连接）。

在每一步中，输出是一个名为N的单列，其值为1（为了保持简单）。同时，除了使用TOP和您的开始和结束日期之间的差异 - TOP(DATEDIFF(DAY，@StartDate，@EndDate) + 1)，我还告诉SQL Server如何生成10,000行。这避免了不必要的工作。我必须将差异加1，以确保两个日期都包含在内。

使用排名函数ROW_NUMBER()，我对生成的每一行添加了增量编号，然后将此增量编号添加到起始日期中，以获取日期列表。由于ROW_NUMBER()从1开始，因此我需要从中减去1，以确保包括起始日期。

然后，只需使用NOT EXISTS来排除已经存在的日期即可。我将上述查询的结果封装在自己的CTE中，称为dates：

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Dates AS
(   SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
            Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @StartDate)
    FROM N3
)
INSERT INTO MyTable ([TimeStamp])
SELECT  Date
FROM    Dates AS d
WHERE NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE d.Date = t.[TimeStamp])

SQL Fiddle上的示例

如果您创建了一个日历表（如链接文章中所述），则可能不需要插入这些额外的行，您可以即时生成结果集，例如：

SELECT  [Timestamp] = c.Date,
        t.[FruitType],
        t.[NumOffered],
        t.[NumTaken],
        t.[NumAbandoned],
        t.[NumSpoiled]
FROM    dbo.Calendar AS c
        LEFT JOIN dbo.MyTable AS t
            ON t.[Timestamp] = c.[Date]
WHERE   c.Date >= @StartDate
AND     c.Date < @EndDate;

补充说明

为回答您的实际问题，你的循环应该编写如下：

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME

SET @StartDate = '2015-01-01'
SET @EndDate = GETDATE()
SET @CurrentDate = @StartDate

WHILE (@CurrentDate < @EndDate)
BEGIN
    IF NOT EXISTS (SELECT 1 FROM myTable WHERE myTable.Timestamp = @CurrentDate)
    BEGIN
        INSERT INTO MyTable ([Timestamp])
        VALUES (@CurrentDate);
    END

    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate); /*increment current date*/
END

SQL Fiddle示例

我不赞成这种方法，仅仅因为某些事情只需要做一次，并不意味着我不应该展示正确的做法。

进一步解释

由于堆叠CTE方法可能已经过于复杂了，我将使用未记录在文档中的系统表master..spt_values来简化它。如果你运行：

SELECT Number
FROM master..spt_values
WHERE Type = 'P';

您会发现您可以获得从0到2047的所有数字。

如果您运行以下命令：

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();


SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P';

您可以获取从起始日期到未来2047天的所有日期。如果您添加了进一步的where子句，您可以将其限制为在结束日期之前的日期：

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();


SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate;

现在你已经在一个基于集合的查询中拥有了所有需要的日期，你可以使用NOT EXISTS来消除已经存在于你的表中的行。

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();


SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate
AND NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE t.[Timestamp] = DATEADD(DAY, number, @StartDate));

最后，您可以使用INSERT将这些日期插入到您的表中。

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

INSERT YourTable ([Timestamp])
SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate
AND NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE t.[Timestamp] = DATEADD(DAY, number, @StartDate));

希望这能在一定程度上表明，基于集合的方法不仅更高效，而且更简单易懂。