MySQL如何查询含当前月及未来5个月的17个月时间区间数据

需求 #

• 获取17个月时间区间的数据：覆盖当前月往前11个月、当前月、未来5个月
• 结果需包含字段：iCurrentMonth（1标识当前月，0为其他）、strCurrentDate（当前日期时间）
• 查询结果用于Highcharts图表，支持区间内数据与当前月之前月份的对比

当前查询语句 #

SELECT
    MONTH(s_order_main.added_date)                  AS iMonth,
    s_order_main.channel                            AS strChannel,
    COUNT(DISTINCT s_order_styles.s_order_style_id) AS iOrderCount,
    SUM(DISTINCT s_order_styles.sales_price)        AS fTurnover
FROM
    s_order_main
INNER JOIN
    s_order_styles ON s_order_styles.s_order_main_id = s_order_main.id
WHERE
    s_order_main.channel
    && s_order_main.order_type = 'pre'
    && YEAR(s_order_main.added_date) = YEAR(CURDATE() - INTERVAL 17 MONTH)
GROUP BY
    MONTH(s_order_main.added_date);

现有结果表 #

期望结果表 #

优化后的查询语句 #

WITH RECURSIVE month_sequence AS (
    -- 起始月份：当前月往前11个月
    SELECT 
        DATE_FORMAT(CURDATE() - INTERVAL 11 MONTH, '%Y-%m-01') AS month_start,
        MONTH(CURDATE() - INTERVAL 11 MONTH) AS iMonth,
        YEAR(CURDATE() - INTERVAL 11 MONTH) AS iYear
    UNION ALL
    -- 递归生成后续16个月（总共17个月）
    SELECT 
        DATE_ADD(month_start, INTERVAL 1 MONTH) AS month_start,
        MONTH(DATE_ADD(month_start, INTERVAL 1 MONTH)) AS iMonth,
        YEAR(DATE_ADD(month_start, INTERVAL 1 MONTH)) AS iYear
    FROM month_sequence
    WHERE month_start <= LAST_DAY(CURDATE() + INTERVAL 5 MONTH)
),
-- 获取所有渠道列表
channel_list AS (
    SELECT DISTINCT channel AS strChannel FROM s_order_main WHERE channel IS NOT NULL
)
SELECT
    ms.iMonth,
    cl.strChannel,
    COALESCE(COUNT(DISTINCT sos.s_order_style_id), 0) AS iOrderCount,
    COALESCE(SUM(DISTINCT sos.sales_price), 0) AS fTurnover,
    -- 当前月标识：1表示当前年月匹配，0否则
    CASE WHEN DATE_FORMAT(ms.month_start, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m') THEN 1 ELSE 0 END AS iCurrentMonth,
    -- 当前日期时间
    CURRENT_TIMESTAMP() AS strCurrentDate
FROM month_sequence ms
CROSS JOIN channel_list cl
-- 左连接订单数据，确保所有月份和渠道都有记录
LEFT JOIN s_order_main som 
    ON DATE_FORMAT(som.added_date, '%Y-%m') = DATE_FORMAT(ms.month_start, '%Y-%m')
    AND som.channel = cl.strChannel
    AND som.order_type = 'pre'
LEFT JOIN s_order_styles sos 
    ON sos.s_order_main_id = som.id
GROUP BY ms.iYear, ms.iMonth, cl.strChannel
ORDER BY ms.month_start, cl.strChannel;

关键优化说明 #

1. 完整月份序列：用递归CTE生成17个月的连续日期范围，避免缺失未来月份或历史月份
2. 多渠道覆盖：通过channel_list获取所有存在的渠道，确保每个渠道在每个月份都有记录
3. 当前月标识：通过年月匹配准确判断当前月，避免仅用月份判断导致跨年错误
4. 空值处理：用COALESCE将无数据的月份订单数和营业额设为0，符合图表展示需求
5. 时间区间修正：原查询仅筛选单一年份，优化后覆盖正确的17个月范围

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