如何打破每个月的日期范围 [英] How to break a date range for each month

问题描述

我是 SQL 初学者.互联网上有很多来源，但我无法实现这一点.假设我有这样的一行:

I'm a beginner in SQL. There are many sources on the internet but I'm not able to achieve this. Let's say I have a row like this:

日期格式为'YYYY-MM-DD':

Date format is 'YYYY-MM-DD':

+---------+--------------+------------+------------+
| name    | course       | sdate      | edate      |
+---------+--------------+------------+------------+
| Tanzeel | SQL Bootcamp | 2019-05-28 | 2019-11-20 |
+---------+--------------+------------+------------+

基本上，这表示 Tanzeel 从 2019 年 5 月 到 2019 年 11 月 参加了 SQL 训练营.(日/日期在这里无关紧要).我想像这样分解这个范围:

Basically this says Tanzeel was enrolled in SQL Bootcamp from May 2019 to Nov 2019. (Day/Date doesn't matters here). I want to break down this range like this:

+---------+--------------+------------+
| name    | course       | enrollMonth|
+---------+--------------+------------+
| Tanzeel | SQL Bootcamp | 2019-05    |
+---------+--------------+------------+
| Tanzeel | SQL Bootcamp | 2019-06    |
+---------+--------------+------------+
| Tanzeel | SQL Bootcamp | 2019-07    |
+---------+--------------+------------+
| Tanzeel | SQL Bootcamp | 2019-08    |
+---------+--------------+------------+
| Tanzeel | SQL Bootcamp | 2019-09    |
+---------+--------------+------------+
| Tanzeel | SQL Bootcamp | 2019-10    |
+---------+--------------+------------+
| Tanzeel | SQL Bootcamp | 2019-11    |
+---------+--------------+------------+

对此的正确方法是什么.请帮我.我的版本是:

What is the correct approach for this. Please help me. My versions are:

+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| admin_tls_version        | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| immediate_server_version | 999999                        |
| innodb_version           | 8.0.21                        |
| original_server_version  | 999999                        |
| protocol_version         | 10                            |
| slave_type_conversions   |                               |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                  | 8.0.21                        |
| version_comment          | MySQL Community Server - GPL  |
| version_compile_machine  | x86_64                        |
| version_compile_os       | Win64                         |
| version_compile_zlib     | 1.2.11                        |
+--------------------------+-------------------------------+

推荐答案

使用递归CTE:

with recursive cte as (
  select name, course, sdate, edate 
  from tablename
  union all
  select name, course, sdate + interval 1 month, edate
  from cte 
  where last_day(sdate) < edate
)
select name, course, date_format(sdate, '%Y-%m') enrollMonth
from cte

请参阅演示.
结果:

See the demo.
Results:

> name    | course       | enrollMonth
> :------ | :----------- | :----------
> Tanzeel | SQL Bootcamp | 2019-05    
> Tanzeel | SQL Bootcamp | 2019-06    
> Tanzeel | SQL Bootcamp | 2019-07    
> Tanzeel | SQL Bootcamp | 2019-08    
> Tanzeel | SQL Bootcamp | 2019-09    
> Tanzeel | SQL Bootcamp | 2019-10    
> Tanzeel | SQL Bootcamp | 2019-11    

这篇关于如何打破每个月的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！