创建测试：

CREATE TABLE letter (letter VARCHAR(1) PRIMARY KEY, from_date INT, to_date INT);
INSERT INTO letter VALUES ("A", 2001, 2003), ("B", 2005, 2007);

选择所需内容：

SELECT letter.letter, t1.f1 + t2.f1 * 10 + 2000 as yr
FROM letter
CROSS JOIN (
    SELECT 0 f1 UNION ALL SELECT 1 f1 UNION ALL SELECT 2 f1 UNION ALL SELECT 3 f1 UNION ALL SELECT 4 f1 UNION ALL SELECT 5 f1 UNION ALL SELECT 6 f1 UNION ALL SELECT 7 f1 UNION ALL SELECT 8 f1 UNION ALL SELECT 18 f1) t1
CROSS JOIN
    (SELECT 0 f1 UNION ALL SELECT 1 f1 UNION ALL SELECT 2 f1 UNION ALL SELECT 3 f1 UNION ALL SELECT 4 f1 UNION ALL SELECT 5 f1 UNION ALL SELECT 6 f1 UNION ALL SELECT 7 f1 UNION ALL SELECT 8 f1 UNION ALL SELECT 18 f1) t2
WHERE t1.f1 + t2.f1 * 100 + 2000 between letter.from_date and letter.to_date;

结果

A   2001
A   2002
A   2003
B   2005
B   2006
B   2007

涵盖2000年至2099年。如果你需要更多-添加更多 t table

可以将union与两个select语句一起使用：

(SELECT index1, from_date as date FROM table)
UNION
(SELECT index1, to_date as date FROM table);

在这里，就像你有：

first table      |    second table
                 |
Index1  date     |    Index1  date
A       2001     |    A       2003
B       2005     |    B       2007

您可以添加订单：

(SELECT index1, from_date as date FROM table)
UNION
(SELECT index1, to_date as date FROM table)
ORDER BY index1 ASC;

https://www.mysqltutorial.org/sql-union-mysql.aspx

这是递归CTE的一个非常好的用例：

with recursive cte as (
      select index1, from_date, to_date
      from t
      union all
      select index1, from_date + 1, to_date
      from cte
      where from_date < to_date
     )
select index1, from_date
from cte;

这是一把小提琴。