求可筛选两个日期值之间年份的SQL查询语句

云上老周

2026-7-13

两个日期之间年份值的SQL查询方案

以下针对不同主流数据库提供对应的查询方案，基于你的基础查询语句扩展：

SQL Server 版本

使用递归CTE生成年份范围，关联原表得到每个记录对应的中间年份：

WITH YearRange AS (
    SELECT YEAR(StartDate) AS YearValue
    FROM dbo.Test
    UNION ALL
    SELECT YearValue + 1
    FROM YearRange
    WHERE YearValue + 1 <= YEAR(DueDate)
)
SELECT DISTINCT t.ID, t.StartDate, t.DueDate, yr.YearValue
FROM dbo.Test t
JOIN YearRange yr ON yr.YearValue BETWEEN YEAR(t.StartDate) AND YEAR(t.DueDate)
ORDER BY t.ID, yr.YearValue;

MySQL 版本

8.0+ 支持递归CTE版本

WITH RECURSIVE YearRange AS (
    SELECT YEAR(StartDate) AS YearValue, ID, StartDate, DueDate
    FROM dbo.Test
    UNION ALL
    SELECT YearValue + 1, ID, StartDate, DueDate
    FROM YearRange
    WHERE YearValue + 1 <= YEAR(DueDate)
)
SELECT DISTINCT ID, StartDate, DueDate, YearValue
FROM YearRange
ORDER BY ID, YearValue;

低版本MySQL（无递归CTE）

借助数字辅助表生成年份范围，可根据实际年份跨度扩展数字表：

SELECT 
    t.ID,
    t.StartDate,
    t.DueDate,
    (YEAR(t.StartDate) + n.num) AS YearValue
FROM dbo.Test t
JOIN (
    SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) n ON (YEAR(t.StartDate) + n.num) <= YEAR(t.DueDate)
ORDER BY t.ID, YearValue;

PostgreSQL 版本

利用generate_series函数直接生成年份序列，语法更简洁：

SELECT 
    t.ID,
    t.StartDate,
    t.DueDate,
    generate_series(YEAR(t.StartDate)::INT, YEAR(t.DueDate)::INT) AS YearValue
FROM dbo.Test t
ORDER BY t.ID, YearValue;