SQL中含日期字段的表连接实现目标视图求助

假设你有两张表：一张是包含每日/指定日期维度的业务表（比如销售记录、库存快照），另一张是产品有效期配置表（包含产品ID、生效起始/结束日期，以及你需要的目标字段如价格、状态等）。以下是针对你的问题的解决方法：

基础连接方案（单条日期匹配单条有效期） #

如果你的业务表每条记录对应一个产品+日期，直接通过产品ID关联，同时添加日期范围筛选即可：

SELECT 
    业务表.product_id,
    业务表.record_date,
    有效期表.target_field -- 替换为你需要的字段（如价格、状态）
FROM 业务表
JOIN 有效期表 
    ON 业务表.product_id = 有效期表.product_id
    AND 业务表.record_date BETWEEN 有效期表.start_date AND 有效期表.end_date

常见失效原因及修正 #

1. 日期类型不匹配：如果业务表日期是DATETIME类型，而有效期表是DATE类型，需要统一格式：

   -- 示例：将DATETIME转为DATE
   AND DATE(业务表.record_date) BETWEEN 有效期表.start_date AND 有效期表.end_date
   

2. 有效期区间逻辑为左闭右开：如果实际业务中end_date是截止日期的前一天（比如有效期到2024-05-30，实际包含到2024-05-29），需调整条件：

   AND 业务表.record_date >= 有效期表.start_date 
   AND 业务表.record_date < DATE_ADD(有效期表.end_date, INTERVAL 1 DAY)
   

多有效期匹配的处理（同一日期对应多条产品生效记录） #

如果同一产品在同一日期存在多条重叠的有效期记录，需要用窗口函数筛选出符合业务逻辑的单条记录（比如最新生效的）：

SELECT 
    product_id,
    record_date,
    target_field
FROM (
    SELECT 
        业务表.product_id,
        业务表.record_date,
        有效期表.target_field,
        -- 按生效日期倒序，取最新的一条
        ROW_NUMBER() OVER (PARTITION BY 业务表.product_id, 业务表.record_date ORDER BY 有效期表.start_date DESC) AS rn
    FROM 业务表
    JOIN 有效期表 
        ON 业务表.product_id = 有效期表.product_id
        AND 业务表.record_date BETWEEN 有效期表.start_date AND 有效期表.end_date
) AS temp
WHERE rn = 1

无业务表时生成全量日期+产品组合 #

如果没有现成的业务日期记录，需要生成指定日期范围内的所有产品+日期组合，再匹配有效期：

WITH 日期序列 AS (
    -- 生成起始到结束的所有日期
    SELECT (SELECT MIN(start_date) FROM 有效期表) AS date_val
    UNION ALL
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
    FROM 日期序列
    WHERE date_val < (SELECT MAX(end_date) FROM 有效期表)
),
产品列表 AS (
    SELECT DISTINCT product_id FROM 有效期表
)
SELECT 
    产品列表.product_id,
    日期序列.date_val,
    有效期表.target_field
FROM 产品列表
CROSS JOIN 日期序列
LEFT JOIN 有效期表 
    ON 产品列表.product_id = 有效期表.product_id
    AND 日期序列.date_val BETWEEN 有效期表.start_date AND 有效期表.end_date
ORDER BY 产品列表.product_id, 日期序列.date_val

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