mysql - 迭代日期 Mysql 循环

Question

三年来，我编写了一个每周迭代的存储过程。但它不起作用，并返回一条模糊的错误消息。

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 18

DELIMITER $$
CREATE PROCEDURE loop_three_years()
BEGIN
    declare y INT default 2016;
    declare m int default 4;
    declare d int default 20;
    WHILE y <= 2019 DO
        WHILE YEARWEEK(concat(y, '-', m, '-', d)) <= 53 DO
            WHILE m < 12 DO
                WHILE (m = 2 and d <= 29) OR (d <=30 and m in(4, 6,9,11)) OR ( m in(1,3,5,7,8,10,12) AND d <= 31) DO
                    set d = d + 7;
                    SELECT YEARWEEK(concat(y, '-', m, '-', d));
                END WHILE;
                set d=1;
        END WHILE;
        set m = 1;
        SET y = y + 1;
    END WHILE;
END
$$

当我将它用作最小部件时，它们可以正常工作，所以我不确定我的重新组装有什么问题。也不确定是否有更好的方法来做到这一点。 (select 只是为了测试，当我使用真正的代码时，它将是一个insert。

Answer 1

Slightly Altered from a previous solution

您可以使用系统中的任何其他表来构建您自己的动态日历/列表，该表的记录数量至少与伪造行号所需的数量一样多。下面的查询将使用像内联程序和声明一样工作的 MySQL @ 变量。我可以用给定的日期开始列表...例如您的 2016-04-20，然后每次迭代，使用基于日期的函数添加 1 周。我不需要知道或关心 28、29(闰年)、30 或 31 天有多少天。

下面“AnyTableThatHasAtLeast156Records”的表引用就是......数据库中至少有 156 条记录的任何表(每年 52 周，3 年)

select
      YEARWEEK( @startDate ) WeekNum,
      @startDate as StartOfWeek,              
      @startDate := date_add( @startDate, interval 1 week ) EndOfWeek
   from 
      ( select @startDate := '2016-04-20') sqlv,
      AnyTableThatHasAtLeast156Records
   limit
      156 

这将为您提供一个包含 156 条记录的列表(前提是您的“anyTable…”一次有 156 条记录。如果您需要将其连接到其他事务表，您可以通过将上面的连接表设为连接表来实现。在这里受益，因为我包括了开始日期和周末，这些可以成为您加入表格的一部分。

例子，在

record   WeekNum   StartOfWeek   EndOfWeek
1        ??        2016-04-20    2016-04-27
2        ??        2016-04-27    2016-05-04
3        ??        2016-05-04    2016-05-11
4        ??        2016-04-11    2016-05-18... etc

通过在起点上增加 1 周，您可以看到它会执行 Ex:星期一到星期一。下面的 JOIN 条件比 EndOfWeek 少。这将说明 UP TO 但不包括结束日期的任何交易......例如 2016-04-26 11:59:59PM 的交易(因此小于 2016-04-27，因为 04/27 是交易的开始下周的交易周期)

select
      Cal.WeekNum,
      YT.YourColumns
   from
      YourTransactionTable YT
         JOIN ( aboveCalendarQuery ) Cal
            on YT.TransactionDate >= Cal.StartOfWeek
            AND YT.TransactionDate < Cal.EndOfWeek
   where
      whatever else

如果您想要的话，您甚至可以对分组进行 sum()，例如按 WeekNum。

希望这是一种更准确、更有效的方式来构建您的日历，以便在您需要时运行并链接到事务。

评论回复。

您可以通过连接到 a (select 1 union select 2 union … select 156)，但您可以选择。 “AnyTable…”的唯一原因是我确信对于任何合理的具有事务的数据库，您将拥有 156 条记录或更容易。它的唯一目的是只允许一行在迭代中循环以动态创建行。

也比您开始时遇到的循环机制更可靠。这没有错，尤其是学习目的，但如果有更有效的方法，那不是更有意义吗？

根据评论的反馈

我不完全了解您要插入的其他表，但是是的，您可以将其用于所有 3000 项操作。提供更多你正在尝试做的事情，我可以调整...与此同时，类似这样的事情...

insert into YourOtherTable
(   someField,
    AnotherField,
    WeekNum 
)
select
      x.someField,
      x.AnotherField,
      z.WeekNum
   from
      Your3000ThingTable x
         JOIN (select
                     YEARWEEK( @startDate ) WeekNum,
                     @startDate as StartOfWeek,              
                     @startDate := date_add( @startDate, interval 1 week ) EndOfWeek
                  from 
                     ( select @startDate := '2016-04-20') sqlv,
                     AnyTableThatHasAtLeast156Records
                  limit
                     156 ) z
            on 1=1
   where
      x.SomeCodition...

通过在 1=1(始终为真)上加入 156 条记录的选择，它将返回 Your3000ThingTable 中任何记录的 156 个条目。所以，如果你有一个包含

的库存项目表

Item  Name
1     Thing1
2     Thing2
3     Thing3

您的最终插入将是

Item   Name     WeekNum
1      Thing1   1
1      Thing1   2
1      Thing1   ...
1      Thing1   156
2      Thing2   1
2      Thing2   2
2      Thing2   ...
2      Thing2   156
3      Thing3   1
3      Thing3   2
3      Thing3   ...
3      Thing3   156

为了预先确认您认为会发生什么，只需在 1=1 上尝试选择/连接，您将看到查询将插入到目标表中的所有记录。