如何用MySQL获取指定用户过去12周（周一至周日）的订单统计？

嘿，我来帮你搞定这三个查询问题！咱们一步步拆解，最后给你一个能满足所有需求的完整方案。

问题分析与解决思路 #

你的原查询有三个核心问题，咱们逐个击破：

1. 无法返回订单数为0的周
   原查询只从shop_orders表取数据，没订单的周自然不会出现在结果里。解决办法是先生成包含过去12周每周一的日期序列表，再用左连接关联订单表，这样空周也能显示出来。

2. 未按周一至周日周期统计
   MySQL默认的WEEKOFYEAR()是按周日作为一周起始（模式0），分组逻辑不够精准。我们可以把每个订单的日期转换为对应周的周一，用这个周一日期来分组，就能严格遵循周一到周日的统计周期。

3. 无法按第1到第12周顺序返回
   原查询用%m/%Y排序，会导致同月份的周顺序混乱，还没法处理跨年场景。直接按生成的周起始日期排序，就能保证从最早的第1周到最近的第12周（或按需调整顺序）。

完整查询语句（MySQL 8.0+ 支持CTE版本） #

WITH weekly_dates AS (
    -- 生成过去12周的每周一日期序列
    SELECT 
        DATE_SUB(
            DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), 
            INTERVAL (n-1) WEEK
        ) AS week_start
    FROM 
        (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
         UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) weeks
)
SELECT 
    COALESCE(COUNT(o.user_id), 0) AS Orders,
    COALESCE(SUM(o.amount), 0) AS Total,
    -- 自定义周的显示格式，比如"第1周 2024/05"
    CONCAT('第', ROW_NUMBER() OVER(ORDER BY w.week_start), '周 ', DATE_FORMAT(w.week_start, '%Y/%m')) AS Week
FROM 
    weekly_dates w
LEFT JOIN 
    shop_orders o ON o.user_id = 123 
    AND o.transaction_date >= w.week_start 
    AND o.transaction_date < DATE_ADD(w.week_start, INTERVAL 7 DAY)
WHERE 
    w.week_start >= DATE_SUB(CURDATE(), INTERVAL 12 WEEK)
ORDER BY 
    w.week_start ASC; -- 从最早的第1周到最近的第12周排序

语句说明 #

• weekly_dates CTE：通过生成1-12的数字序列，计算出过去12周的每个周一日期。DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)能获取当前周的周一，再依次往前推0-11周得到完整序列。
• 左连接逻辑：将生成的周序列与订单表关联，只统计该用户在对应周（周一到下周一前一天，即周日）的订单。
• COALESCE函数：把空值转为0，确保无订单的周也能显示Orders=0、Total=0。
• ROW_NUMBER()：自动生成第1到第12周的序号，完美匹配你的排序需求。

兼容MySQL 5.x版本的写法（无CTE） #

如果你的MySQL版本不支持CTE，可以用临时变量替代：

SELECT 
    COALESCE(COUNT(o.user_id), 0) AS Orders,
    COALESCE(SUM(o.amount), 0) AS Total,
    CONCAT('第', @row_num := @row_num + 1, '周 ', DATE_FORMAT(w.week_start, '%Y/%m')) AS Week
FROM 
    (SELECT 
        DATE_SUB(
            DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), 
            INTERVAL (n-1) WEEK
        ) AS week_start
     FROM 
         (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
          UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) weeks
    ) w
CROSS JOIN 
    (SELECT @row_num := 0) rn
LEFT JOIN 
    shop_orders o ON o.user_id = 123 
    AND o.transaction_date >= w.week_start 
    AND o.transaction_date < DATE_ADD(w.week_start, INTERVAL 7 DAY)
WHERE 
    w.week_start >= DATE_SUB(CURDATE(), INTERVAL 12 WEEK)
ORDER BY 
    w.week_start ASC;

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