如何在SQL中无需硬编码年份计算日期区间内各年天数？

云原生阿岚

2026-8-1

无需硬编码年份的SQL日期区间年天数计算方案

核心思路

自动生成目标日期区间覆盖的所有年份序列，无需手动指定年份
对每个年份，计算该年份的自然日期范围（当年1月1日至12月31日）与目标区间的重叠天数

PostgreSQL 实现示例

假设你有一张名为 date_ranges 的表，包含 start_date（起始日期）和 end_date（结束日期）字段：

WITH year_series AS (
    SELECT generate_series(
        EXTRACT(YEAR FROM start_date)::INT,
        EXTRACT(YEAR FROM end_date)::INT
    ) AS year
    FROM date_ranges
)
SELECT
    ys.year,
    GREATEST(
        0,
        LEAST(
            (ys.year || '-12-31')::DATE,
            dr.end_date
        ) - GREATEST(
            (ys.year || '-01-01')::DATE,
            dr.start_date
        ) + 1
    ) AS total_days
FROM year_series ys
CROSS JOIN date_ranges dr
ORDER BY ys.year;

MySQL 实现示例

MySQL 没有原生的 generate_series 函数，可通过递归CTE生成年份序列：

WITH RECURSIVE year_series AS (
    SELECT YEAR(start_date) AS year
    FROM date_ranges
    UNION ALL
    SELECT year + 1
    FROM year_series ys
    JOIN date_ranges dr ON ys.year < YEAR(dr.end_date)
)
SELECT
    ys.year,
    GREATEST(
        0,
        DATEDIFF(
            LEAST(STR_TO_DATE(CONCAT(ys.year, '-12-31'), '%Y-%m-%d'), dr.end_date),
            GREATEST(STR_TO_DATE(CONCAT(ys.year, '-01-01'), '%Y-%m-%d'), dr.start_date)
        ) + 1
    ) AS total_days
FROM year_series ys
JOIN date_ranges dr ON ys.year BETWEEN YEAR(dr.start_date) AND YEAR(dr.end_date)
ORDER BY ys.year;