问题

我有以下数据，作为示例：

Name | Value | startdate | enddate
1 | 10 | 2023-04-01 | 2023-06-30
2 | 99 | 2023-03-01 | 2023-05-01

我需要获得以下输出：

Name | Value | date
1 | 10 | 2023-04
1 | 10 | 2023-05
1 | 10 | 2023-06
2 | 99 | 2023-03
2 | 99 | 2023-04
2 | 99 | 2023-05

简而言之，对于每个Name列，在日期范围内的每个月都生成一个具有相同Value的新行。

尝试过一些解决方案，参见[这里][1]和[这里][2]，但效果不太好，因为它们不产生相同的预期结果，有时候如果StartDate是2022年，EndDate是2023年，一些提到的解决方案会出问题。

到目前为止我做了什么：

```mysql
SELECT
    value,
    DATE_FORMAT(startdate, "%Y-%m") AS startMonth,
    DATE_FORMAT(enddate, "%Y-%m") AS endMonth
FROM
    table
GROUP BY
    --这是我的困扰之处

一个存储过程也会有所帮助！只要它生成这个输出。

<details>
<summary>英文:</summary>

I have the following data, as an example:

Name | Value | startdate | enddate
1 | 10 | 2023-04-01 | 2023-06-30
2 | 99 | 2023-03-01 | 2023-05-01

I need to get the following output:

Name | Value | date
1 | 10 | 2023-04
1 | 10 | 2023-05
1 | 10 | 2023-06
2 | 99 | 2023-03
2 | 99 | 2023-04
2 | 99 | 2023-05

Which, in resume, is a new row with the same Value for every month between the date range for every Name column.

Tried some solutions seen [here][1] and [here][2], but didn't turned out so great, since it's not the same expected result, and sometimes having the startDate on 2022 and EndDate on 2023 for example breaks some mentioned solutions.

What I made so far:


```mysql
SELECT
	value,
	DATE_FORMAT(startdate, "%Y-%m") AS startMonth,
	DATE_FORMAT(enddate, "%Y-%m") AS endMonth

FROM
	table

GROUP BY
    --Here's my stuggle

A procedure would help too! As long as it generates that output.

答案1

得分: 1

以下是您要翻译的内容：

with recursive dates(name, date) as (
  select name, startdate from mytable where name = 1
  union all
  select dates.name, dates.date + interval 1 month from dates join mytable using (name)
  where dates.date < mytable.enddate
)
select * from dates;

输出：

+------+------------+
| name | date       |
+------+------------+
|    1 | 2023-04-01 |
|    1 | 2023-05-01 |
|    1 | 2023-06-01 |
|    1 | 2023-07-01 |
+------+------------+

这个查询用递归的方式创建了一个日期序列，起始于指定的开始日期，直到满足终止条件。这个技巧是使用通用表达式来实现的。

此外，您提供的链接是MySQL中的代码示例，展示了如何在MySQL中使用通用表达式来生成系列数据。

with recursive dates(name, date) as (
  select name, startdate from mytable where name = 1
  union all
  select dates.name, dates.date + interval 1 month from dates join mytable using (name)
  where dates.date < mytable.enddate
)
select * from dates;

+------+------------+
| name | date       |
+------+------------+
|    1 | 2023-04-01 |
|    1 | 2023-05-01 |
|    1 | 2023-06-01 |
|    1 | 2023-07-01 |
+------+------------+