汇率查询SQL优化需求：获取指定货币当月数据，无则取上月

核心逻辑 #

先提取每个货币每月的月末最新汇率（保证每个月仅保留最后一条数据），再针对指定货币筛选出所有月份不晚于目标月份的月末记录，最后按月份倒序取第一条——这样就能自动实现「优先当月数据，当月无数据则取上月」的需求。

调整后的SQL代码 #

SELECT e.id, e.date, e.local_currency_id, e.rate, e.downloaddate
FROM exchange_rates e
-- 子查询：获取每个货币每个月的月末日期
JOIN (
    SELECT 
        local_currency_id,
        date_trunc('month', date) AS month,
        MAX(date) AS max_date
    FROM exchange_rates
    GROUP BY local_currency_id, date_trunc('month', date)
) AS m_date 
    ON e.local_currency_id = m_date.local_currency_id 
    AND e.date = m_date.max_date
WHERE 
    e.local_currency_id = :localCurrencyId
    -- 筛选出不晚于目标月份的记录
    AND m_date.month <= date_trunc('month', :currentDate)
-- 按汇率所属月份倒序，取最近的一条有效数据
ORDER BY m_date.month DESC
LIMIT 1;

关键调整说明 #

• 子查询新增month字段，明确按汇率日期的月份分组，方便后续精准筛选月份范围
• 移除原SQL中e.date >=:currentDate的限制，改为基于月份的筛选，确保能包含上月及更早的有效月末数据
• 排序依据改为汇率所属的month，而非downloaddate的月份，逻辑更贴合业务需求
• 通过ORDER BY month DESC LIMIT 1自动获取最近的有效数据，无需额外编写判断当月是否存在数据的逻辑

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