SQL查询，将数据限制在2个日期之间

Question

在SQL中，我将编写一个查询，插入两个日期之间的所有数据，我还想将其放入1000个批次中，但由于这两天之间的数据数量超过了我的限制，我将写一个循环，使数据放入的时间更短。这是我的代码：

DECLARE @StartDate DATETIME = CAST('2021-06-02 01:00:00.000' AS DATETIME)
DECLARE @EndDate DATETIME = CAST('2021-06-23 01:00:00.000' AS DATETIME)
DECLARE @RealRowCount INT = (SELECT DISTINCT SUM(@@ROWCOUNT) OVER() FROM GetReport (
@StartDate, @EndDate))
DECLARE @TransactionCount INT = (SELECT DISTINCT TransactionCount FROM GetReport (
@StartDate, @EndDate))
WHILE @RealRowCount < @TransactionCount
BEGIN
DECLARE @DiffDate INT = (SELECT DATEDIFF(DAY, @StartDate, @EndDate))
SET @EndDate =  DATEADD(DAY, @DiffDate/2 ,@StartDate)
SELECT *,@StartDate, @EndDate  FROM GetReport (@StartDate, @EndDate)
END

PS：我想找到日期的中间，然后把它们改成新的EneDate和StartDate，但这里有问题！

Answer 1

您的问题不是很清楚。假设您在两个日期之间有10000条记录，并且不希望一次检索超过1000条记录。在这种情况下，可以使用分页。在程序代码和SQL中。

DECLARE @StartDate DATETIME = CAST('2021-06-02 01:00:00.000' AS DATETIME)
DECLARE @EndDate DATETIME = CAST('2021-06-23 01:00:00.000' AS DATETIME)
DECLARE @RealRowCount INT = (SELECT DISTINCT COUNT(*) FROM Products WHERE InsertDate BETWEEN @StartDate AND @EndDate)

DECLARE @Counter INT = 0
WHILE @Counter <= @RealRowCount
BEGIN
SELECT *
FROM Products
WHERE InsertDate BETWEEN @StartDate AND @EndDate
ORDER BY InsertDate
OFFSET @Counter ROWS -- skip @Counter rows
FETCH NEXT 1000 ROWS ONLY -- take 1000 rows
SET @Counter = @Counter + 1000
END

或者，您可以获取两个日期之间的时间差，并在特定步骤中每次添加开始日期，然后检索该日期的数据。

例如，日期差为20天。每次将开始日期增加5步，直到使用结束日期的开始日期

Answer 2

我创建另一个表来放置日期，如果这个表有任何行，我可以获得"EndDate"，但如果它没有任何记录，我只使用我指定的日期。AccSync是我插入记录详细信息的表，AccTransformation是我要插入所有记录的表。

DECLARE @Count INT = (SELECT COUNT(*) FROM [AccTransaction])
DECLARE @Flag BIT = (SELECT IIF(@Count > 1, 1, 0))
DECLARE @End DATETIME = GETDATE();
DECLARE @Start DATETIME
IF(@Flag = 0)
BEGIN
SET @Start = CAST('2021-03-08' AS DATETIME2);       
SET @Flag = 1
END
ELSE IF(@Flag = 1)
BEGIN
SET @Start = (SELECT TOP 1 EndDate FROM (SELECT EndDate FROM [AccSync] ORDER BY ActionDate DESC OFFSET 0 ROW) AS TT);
END 
DECLARE @RealRowCount INT = (SELECT DISTINCT SUM(@@ROWCOUNT) FROM [GetReport] (@Start, @End));
DECLARE @TransactionCount INT = (SELECT DISTINCT TransactionCount FROM [GetReport] (@Start, @End));
----------------------------------------------------------------------------------------------
WHILE (@RealRowCount <> @TransactionCount)
BEGIN
DECLARE @DiffDate INT = (SELECT DATEDIFF(SECOND, @Start, @End))

SET @End = DATEADD(SECOND, (@DiffDate/2), @Start)
SET @RealRowCount = (SELECT DISTINCT SUM(@@ROWCOUNT) FROM [GetReport] (@Start, @End))
SET @TransactionCount  = (SELECT DISTINCT TransactionCount FROM [GetReport] (@Start, @End))
END
----------------------------------------------------------------------------------------------
INSERT INTO [AccTransaction]
SELECT *
FROM [GetReport](@Start, @End)
----------------------------------------------------------------------------------------------
INSERT INTO [AccSync]
VALUES(NEWID(), GETDATE(), @Start, @End, ISNULL(@TransactionCount,0), DATEDIFF(SECOND, @Start, @End))