a WITH workaround for MySQL-CodePudding

How to convert this with part to MySQL?

SET @start  = '20210101';
SET @end  = '20211231';
        
WITH cte AS   
(  
    SELECT dt = DATEADD(DAY, -(DAY(@start) - 1), @start)  
    UNION ALL  
    SELECT DATEADD(MONTH, 1, dt)  
    FROM cte  
    WHERE dt < DATEADD(DAY, -(DAY(@end) - 1), @end)  
)
SELECT DATENAME(MONTH,dt) Name, DAY(EOMONTH(dt)) as Days into RESULT_TABLE
FROM cte

P.S. as far as I know I cant use WITH in MySQL since version 8.0. But what about version prior to 8.0?

CodePudding user response：

In mysql 8 or mariadb 10.2 , you would do this like:

WITH RECURSIVE cte AS (
    SELECT DATE_SUB(@start, INTERVAL DAY(@start) - 1 DAY) AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 MONTH)
    FROM cte
    WHERE dt < DATE_SUB(@end, INTERVAL DAY(@end) - 1 DAY)
)
SELECT MONTHNAME(dt) AS Name, DAY(LAST_DAY(dt)) AS Days
FROM cte;

But I'm not sure I understand what the "into RESULT_TABLE" was doing.

CodePudding user response：

You can create a table from a recursive query this way:

SET @start  = '20210101';
SET @end  = '20211231';

CREATE TABLE result_table AS
        
WITH RECURSIVE cte(dt) AS   
(  
    SELECT DATE(@start)
    
    UNION ALL  
    
    SELECT DATE_ADD(dt, INTERVAL 1 MONTH)
    FROM cte  
    WHERE DATE_ADD(dt, INTERVAL 1 MONTH) < @end  
)

SELECT MONTHNAME(dt) AS Name, DAY(LAST_DAY(dt)) AS Days
FROM cte;