Oracle SQL实现日期区间ID匹配及同Desc取小ID需求

原始数据表格 #

期望结果（2019年1月至2022年9月） #

需求规则 #

• 生成**2019年1月（01.2019）至2022年9月（09.2022）**的月度结果集
• 仅保留日期处于FromDate与ToDate区间内的ID
• 同一Desc对应多个有效ID时，选取编号最小的ID

解决方案SQL #

WITH date_range AS (
    -- 生成目标时间范围内的所有月份，格式转为MM.YYYY
    SELECT TO_CHAR(ADD_MONTHS(TO_DATE('01.2019', 'MM.YYYY'), LEVEL - 1), 'MM.YYYY') AS month_date
    FROM DUAL
    CONNECT BY ADD_MONTHS(TO_DATE('01.2019', 'MM.YYYY'), LEVEL - 1) <= TO_DATE('09.2022', 'MM.YYYY')
),
valid_matches AS (
    -- 关联原始表筛选有效ID，按月份和Desc分组取最小ID
    SELECT 
        dr.month_date AS "Date",
        MIN(t.ID) AS ID,
        t."Desc"
    FROM date_range dr
    JOIN your_table t 
        ON TO_DATE(dr.month_date, 'MM.YYYY') BETWEEN TO_DATE(t.FromDate, 'MM.YYYY') AND TO_DATE(t.ToDate, 'MM.YYYY')
    GROUP BY dr.month_date, t."Desc"
)
-- 按日期和ID排序输出最终结果
SELECT "Date", ID, "Desc"
FROM valid_matches
ORDER BY "Date", ID;

说明 #

1. 将your_table替换为实际存储原始数据的表名
2. 使用CONNECT BY生成连续月份序列，无需依赖额外日历表
3. 通过TO_DATE转换字符串日期为日期类型，确保区间比较的准确性
4. 利用MIN(ID)实现同一Desc下选取编号最小ID的规则

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