Oracle查询：提取指定参数范围与表内时间段的所有日期

这个需求我碰到过好几次，核心就是要找出同时满足两个条件的日期：一是在你传入的start_date和end_date区间内，二是这个日期被表中至少一个时间段（FROM到TO）包含。下面我分几种常用数据库来给出具体实现，你可以对应自己的场景来用：

1. PostgreSQL 版本 #

PostgreSQL自带的generate_series函数生成日期序列特别方便，省了不少事。假设你的表名叫time_ranges，为了避免和关键字冲突，我把列名改成range_from和range_to（如果你的表就是用FROM/TO当列名，记得用双引号包裹，比如"FROM"）：

WITH date_series AS (
    SELECT generate_series(
        '2013-12-25'::DATE,
        '2014-02-10'::DATE,
        '1 day'::INTERVAL
    )::DATE AS target_date
)
SELECT DISTINCT target_date
FROM date_series
JOIN time_ranges
    ON target_date BETWEEN time_ranges.range_from AND time_ranges.range_to
ORDER BY target_date;

小解释： #

• generate_series直接帮你生成指定起始到结束的所有日期，按天递增
• 用JOIN关联你的时间段表，筛选出落在任意时间段里的日期
• 加DISTINCT是因为同一个日期可能被多个重叠的时间段匹配到，避免返回重复结果
• 最后排序让结果更直观

2. MySQL 版本 #

MySQL分两种情况，8.0及以上支持递归CTE，低版本得用数字表辅助：

方法一：递归CTE（MySQL 8.0+） #

WITH RECURSIVE date_series AS (
    SELECT '2013-12-25' AS target_date
    UNION ALL
    SELECT DATE_ADD(target_date, INTERVAL 1 DAY)
    FROM date_series
    WHERE target_date < '2014-02-10'
)
SELECT DISTINCT target_date
FROM date_series
JOIN time_ranges
    ON target_date BETWEEN time_ranges.range_from AND time_ranges.range_to
ORDER BY target_date;

方法二：数字表辅助（低版本MySQL） #

如果你的MySQL版本不支持递归，先建一个简单的数字表（比如存0到1000的数字，足够覆盖大部分日期范围），然后用它生成日期：

-- 假设你已经有一个叫numbers的表，里面有num列，值从0开始
SELECT DISTINCT DATE_ADD('2013-12-25', INTERVAL num DAY) AS target_date
FROM numbers
JOIN time_ranges
    ON DATE_ADD('2013-12-25', INTERVAL num DAY) BETWEEN time_ranges.range_from AND time_ranges.range_to
WHERE DATE_ADD('2013-12-25', INTERVAL num DAY) <= '2014-02-10'
ORDER BY target_date;

3. SQL Server 版本 #

SQL Server用递归CTE也能轻松生成日期序列，注意如果日期范围超过100天，要加OPTION (MAXRECURSION 0)来解除默认递归次数限制：

WITH date_series AS (
    SELECT CAST('2013-12-25' AS DATE) AS target_date
    UNION ALL
    SELECT DATEADD(DAY, 1, target_date)
    FROM date_series
    WHERE target_date < CAST('2014-02-10' AS DATE)
)
SELECT DISTINCT target_date
FROM date_series
JOIN time_ranges
    ON target_date BETWEEN time_ranges.range_from AND time_ranges.range_to
ORDER BY target_date
OPTION (MAXRECURSION 0);

几个关键提醒 #

• 尽量别用FROM、TO这种SQL关键字当列名，不然每次写都要加转义符，麻烦得很
• DISTINCT一定要加，不然重叠时间段会导致同一个日期返回多次
• 确保所有日期列的类型都是纯日期类型（比如DATE），别带时间部分，不然可能出现匹配错误
• 如果要动态传入参数，把硬编码的日期换成对应的参数就行，比如PostgreSQL用$1、$2，SQL Server用@start_date、@end_date

比如PostgreSQL的动态参数版本：

WITH date_series AS (
    SELECT generate_series(
        $1::DATE,
        $2::DATE,
        '1 day'::INTERVAL
    )::DATE AS target_date
)
SELECT DISTINCT target_date
FROM date_series
JOIN time_ranges
    ON target_date BETWEEN time_ranges.range_from AND time_ranges.range_to
ORDER BY target_date;

内容的提问来源于stack exchange，提问作者Dreamer