如何在MySQL中按周筛选月度数据并生成每周总计？

针对你提到的2023年1月业务数据按周分组、每周后追加总计行的需求，以下是两种可行的实现方案，解决ROLLUP未达预期的问题：

方案一：正确使用ROLLUP函数 #

如果之前ROLLUP效果不符合预期，大概率是分组维度或排序逻辑有误。以下SQL通过明确周分组键，结合GROUPING()函数识别总计行，并通过排序确保周总计紧跟该周明细：

SELECT
  CASE
    WHEN GROUPING(week_start) = 1 THEN '月度总计'
    WHEN GROUPING(campaign_name) = 1 THEN CONCAT('周', WEEK(week_start), '总计')
    ELSE campaign_name
  END AS campaign_name,
  DATE_FORMAT(week_start, '%Y-%m-%d') AS week_start,
  SUM(total_call) AS total_call,
  SUM(total_answered_call) AS total_answered_call,
  SUM(total_abandon_call) AS total_abandon_call
FROM (
  -- 子查询：将每条记录映射到对应周的周一（可根据业务调整周起始日）
  SELECT
    DATE_SUB(Date, INTERVAL WEEKDAY(Date) DAY) AS week_start,
    campaign_name,
    total_call,
    total_answered_call,
    total_abandon_call
  FROM your_table_name
  WHERE Date BETWEEN '2023-01-01' AND '2023-01-31'
) AS weekly_data
GROUP BY week_start, campaign_name WITH ROLLUP
-- 过滤无效行，仅保留周明细、周总计、月度总计
HAVING week_start IS NOT NULL OR GROUPING(week_start) = 1
-- 确保排序逻辑：先按周，再让明细行在前、总计行在后
ORDER BY
  week_start,
  GROUPING(campaign_name);

关键说明： #

• DATE_SUB(Date, INTERVAL WEEKDAY(Date) DAY)：将日期转换为对应周的周一（若业务以周日为周起始，可替换为DATE_SUB(Date, INTERVAL (WEEKDAY(Date)+1)%7 DAY)）
• GROUPING()函数：判断当前行是否为ROLLUP生成的总计行，GROUPING(campaign_name)=1表示该行是当前周的总计，GROUPING(week_start)=1表示月度总计
• 排序逻辑：通过GROUPING(campaign_name)让明细行（值为0）排在总计行（值为1）之前

方案二：用UNION ALL手动拼接明细与总计 #

如果ROLLUP的排序或总计行格式仍不符合需求，可通过UNION ALL分别查询明细行和总计行，再通过排序字段控制顺序：

-- 1. 查询每周的campaign明细
SELECT
  DATE_SUB(Date, INTERVAL WEEKDAY(Date) DAY) AS week_start,
  campaign_name,
  SUM(total_call) AS total_call,
  SUM(total_answered_call) AS total_answered_call,
  SUM(total_abandon_call) AS total_abandon_call,
  0 AS sort_order -- 明细行排序优先级高
FROM your_table_name
WHERE Date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY week_start, campaign_name

UNION ALL

-- 2. 查询每周的总计行
SELECT
  DATE_SUB(Date, INTERVAL WEEKDAY(Date) DAY) AS week_start,
  CONCAT('周', WEEK(Date), '总计') AS campaign_name,
  SUM(total_call) AS total_call,
  SUM(total_answered_call) AS total_answered_call,
  SUM(total_abandon_call) AS total_abandon_call,
  1 AS sort_order -- 总计行排序优先级低
FROM your_table_name
WHERE Date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY week_start

-- 最终排序：先按周，再按sort_order确保明细在前、总计在后
ORDER BY week_start, sort_order, campaign_name;

关键说明： #

• sort_order字段：明确区分明细行和总计行的排序顺序，确保每组明细后紧跟对应周的总计
• 可直接修改总计行的campaign_name显示文本，适配业务需求的格式

注意事项 #

• 替换your_table_name为实际业务表名
• 若Date字段为datetime类型，需先用DATE(Date)转换为日期类型后再处理
• 周起始日可通过调整WEEKDAY相关逻辑修改，适配不同业务的周定义

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