mysql - 选择 while 而不是 While select 导致问题

Question

我正在处理范围日期查询，并尝试调整循环规则，但我遇到了一些问题:

采取以下措施:

DROP PROCEDURE
IF EXISTS test;
CREATE PROCEDURE test ( IN start_date DATE ) BEGIN
    DECLARE group_name VARCHAR ( 10 ) DEFAULT 'clientA';
    DECLARE service_name VARCHAR ( 10 ) DEFAULT 'serviceA';
    WHILE ( start_date < CURDATE( ) && SUBDATE( start_date, INTERVAL - 2 WEEK ) < CURDATE( ) ) DO
        SELECT start_date AS 'Start Day',   SUBDATE( start_date, INTERVAL - 2 WEEK ) AS 'End Day';
        SET start_date = SUBDATE( start_date, INTERVAL - 2 WEEK );
    END WHILE;
END;

这选择从起点到今天的开始和结束日期:

CALL test ( '2019-08-29' );

返回 5 个结果:

• 08/29 和 09/12
• 09/12 和 09/26
• 09/26 和 10/10
• 10/10 和 10/24
• 10/24 和 11/7

这就是我想要的，但不是 5 个结果。我希望其中的每一行都作为一个结果中的行。我认为最好的方法是通过子查询，内部查询运行循环并进行选择，但外部查询充当包装器将它们限制在一组中。

我有以下代码:

    DROP PROCEDURE
    IF EXISTS test;
    CREATE PROCEDURE test ( IN start_date DATE ) BEGIN
        DECLARE group_name VARCHAR ( 10 ) DEFAULT 'clientA';
        DECLARE service_name VARCHAR ( 10 ) DEFAULT 'serviceA';
        SELECT * FROM (WHILE ( start_date < CURDATE( ) && SUBDATE( start_date, INTERVAL - 2 WEEK ) < CURDATE( ) ) DO
            SELECT start_date AS 'Start Day',   SUBDATE( start_date, INTERVAL - 2 WEEK ) AS 'End Day';
            SET start_date = SUBDATE( start_date, INTERVAL - 2 WEEK );
        END WHILE;
)
    END;

但这给了我:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS 'Result' FROM ( WHILE ( start_date < CURDATE( ) && SUBDATE( start_date, IN' at line 4

我觉得我的语法有一些小问题，但我很难理解到底是什么。任何指导都会很棒!

Answer 1

您可以使用 MySQL @ 变量创建日期事件的动态列表，并加入到结果集中具有您期望的行数的任何表...例如:如果动态中需要 5、10 或 1000 条记录结果。

select
      -- whatever latest date is BECOMES the Begin Date
      @beginDT BeginDate,
      -- now, add 2 weeks to the @beginDT variable and save as the END Date
      @beginDT := date_add( @beginDT, interval 2 week ) EndDate
   from
      -- pick any table that has as many 2-week cycles as you expect.
      -- ex: if you wanted 1 yr, you would need any table with 26 or 27 records
      AnyTableWithManyRecords,
      -- start the query with your starting date, alias sqlvars is just place-holder
      -- and will only prepare the variable and be one row for rest of query
      ( select @beginDT := '2019-08-29' ) sqlvars
   having
      -- having will stop until your maximum date of interest
      BeginDate < curdate()
   -- but limit to 100 so you don't query against table of millions of records.
   -- how many records do you REALLY need to go through... again, 26 biweekly = 1 year
   -- this limit of 100 would allow for almost 4 yrs worth
   limit 100;

然后，如果您想要来自其他表的数据，您可以将上面的结果集作为自己的结果加入，例如

select
      SOT.WhateverColumns
   from
      ( above query ) MyDates
         JOIN SomeOtherTable SOT
            on MyDates.BeginDate <= SOT.SomeDate
           AND SOT.SomeDate < MyDates.EndDate