如何修改SQL查询：无指定日期记录时返回最近可用日期价格

现有Items表结构及数据 #

item_id  |  date       | item_price |
---------+-------------+--------------
1        | 2022-12-05  |     15     |
2        | 2022-02-14  |     12     |
1        | 2022-11-12  |     50     |
4        | 2022-01-21  |     13     |
1        | 2021-12-12  |     10     |
6        | 2021-12-27  |     83     |

原查询SQL语句 #

原SQL仅匹配精确日期的价格，无法处理目标日期无记录的情况：

SELECT 
    items.item_id AS id,
    items.item_price AS weekAgoPrice,
    items2.item_price AS monthAgoPrice
FROM
    items
LEFT JOIN
    items items2 ON items2.item_id = items.item_id
    AND items2.date = DATE_SUB(CURDATE(), INTERVAL 30 DAY)
WHERE
    items.item_id = '1'
        AND items.date = DATE_SUB(CURDATE(), INTERVAL 7 DAY);

需求 #

调整查询逻辑：

• 若指定item_id无7天前的价格记录，依次查找6天前、5天前……的记录，取最接近7天前的有效价格
• 同理，无30天前的价格记录时，依次查找29天前、28天前……的记录，取最接近30天前的有效价格

修改后的SQL语句 #

通过子查询筛选时间范围内的最新记录，实现需求逻辑：

SELECT
    t1.item_id AS id,
    t1.item_price AS weekAgoPrice,
    t2.item_price AS monthAgoPrice
FROM
    (
        -- 取最接近7天前的有效价格
        SELECT item_id, item_price
        FROM items
        WHERE item_id = '1'
          AND date <= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
        ORDER BY date DESC
        LIMIT 1
    ) t1
LEFT JOIN
    (
        -- 取最接近30天前的有效价格
        SELECT item_id, item_price
        FROM items
        WHERE item_id = '1'
          AND date <= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        ORDER BY date DESC
        LIMIT 1
    ) t2 ON t1.item_id = t2.item_id;

逻辑说明 #

1. 子查询t1：筛选item_id为1、日期不晚于7天前的所有记录，按日期倒序取第一条，即最接近7天前的有效价格
2. 子查询t2：同理，筛选日期不晚于30天前的记录，取最接近30天前的有效价格
3. 用LEFT JOIN关联两个子查询，确保即使30天前无匹配记录，仍能返回7天前的结果
4. 若需要动态指定item_id，将'1'替换为对应参数即可

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