<p>我有一个如下所示的表：</p> <table class="s-table"> <thead> <tr> <th>姓名</th> <th>INT_VALUE</th> <th>开始</th> <th>END</th> </tr> </thead> <tbody> <tr> <td>一个</td> <td>100</td> <td>2013-11-16</td> <td>2014-11-16</td> </tr> </tbody> </table> <p>目标是输出如下：</p> <table class="s-table"> <thead> <tr> <th>姓名</th> <th>INT_VALUE</th> <th>开始</th> <th>END</th> <th>间隔</th> </tr> </thead> <tbody> <tr> <td>一个</td> <td>100</td> <td>2013-11-16</td> <td>2014-11-16</td> <td>11-2013</td> </tr> <tr> <td>一个</td> <td>100</td> <td>2013-11-16</td> <td>2014-11-16</td> <td>2013年12月</td> </tr> <tr> <td>一个</td> <td>100</td> <td>2013-11-16</td> <td>2014-11-16</td> <td>1-2014</td> </tr> <tr> <td>一个</td> <td>100</td> <td>2013-11-16</td> <td>2014-11-16</td> <td>2014年2月</td> </tr> </tbody> </table> <p>我已经开始使用这个。但是我不确定如何使用表中的START和END日期来确定计算之间的月份列表的开始和结束。</p> <pre class="brush:php;toolbar:false;">SELECT START, calendar, COUNT(1) FROM table1 AS t1 RIGHT JOIN (SELECT row_number() OVER (ORDER BY SEQ4()) AS MONTHS , TO_DATE(DATEADD(MONTH, MONTHS, '2019-05-01')) AS calendar FROM TABLE(GENERATOR(rowcount=>80))) ON t1.START = calendar GROUP BY 1, 2 ORDER BY 2, 1 ;</pre> <p>这里的目标是将int值与范围开始到结束之间的每个月份关联起来。</p>

WITH range_of_numbers AS (
    SELECT 
        row_number() OVER (ORDER BY SEQ4())-1 AS rn
    FROM TABLE(GENERATOR(rowcount=>80))
)
SELECT 
    t1.name, 
    t1.int_value, 
    t1.start,
    t1.end,
    DATEADD(MONTH, r.rn, t1.start) as interval
FROM table1 AS t1
JOIN range_of_numbers as r
    ON date_diff('month', t1.START, t1.end) <= between r.rn
ORDER BY 2,1,3;

CREATE TABLE dates AS 
SELECT 
     DATEADD(MONTH, row_number() OVER (ORDER BY SEQ4())-1, '1980-01-01') as month_date
FROM TABLE(GENERATOR(rowcount=>8000))

FROM table1 AS t1
JOIN dates as d
    ON d.month_date BETWEEN t1.START AND t1.end

WITH RECURSIVE
cte AS ( SELECT name, int_value, start, `end`, 
                1 rownum, DATE_FORMAT(start, '%m-%Y') `interval`
         FROM source_table
         UNION ALL
         SELECT name, int_value, start, `end`, 
                1 + rownum, DATE_FORMAT(start + INTERVAL rownum MONTH, '%m-%Y')
         FROM cte
         WHERE start + INTERVAL rownum - 1 MONTH < `end` )
         
SELECT name, int_value, start, `end`, `interval`
FROM cte
ORDER BY rownum;