如何在MySQL中生成月度及周度报表并按周期展示数据？

嘿，我来帮你搞定这个按周和月维度展示投诉数据的需求！看了你的tblcomplain表结构，dateposted字段刚好能用来做时间维度的聚合，下面给你分情况提供具体的解决方案：

如果需要按年份+月份来汇总投诉数据（比如每个月的总投诉数、各状态投诉量），可以用这个SQL查询：

-- 月度统计：按年月分组，统计总投诉数及各状态数量
SELECT
    YEAR(dateposted) AS post_year,
    MONTH(dateposted) AS post_month,
    MONTHNAME(dateposted) AS month_name, -- 可选，显示"January"这类月份名称更友好
    COUNT(id) AS total_complaints,
    SUM(CASE WHEN status = '已解决' THEN 1 ELSE 0 END) AS resolved_complaints, -- 替换为你实际的状态值
    SUM(CASE WHEN status = '待处理' THEN 1 ELSE 0 END) AS pending_complaints
FROM tblcomplain
GROUP BY post_year, post_month, month_name
ORDER BY post_year DESC, post_month DESC;

这个查询会把数据按年份和月份分组，不仅能算出每个月的总投诉数，还能统计不同状态的投诉量——你只需要把'已解决'、'待处理'换成你表中实际的status值就行。最后按年份和月份倒序排列，最新的数据会排在最前面。

周度统计需要注意不同数据库的周函数略有差异，下面给你主流数据库的写法：

MySQL版本 #

-- MySQL 周度统计：按年份+周数分组，默认周日为一周开始；若要周一开头，把WEEK(dateposted)改成WEEK(dateposted, 1)
SELECT
    YEAR(dateposted) AS post_year,
    WEEK(dateposted) AS post_week,
    CONCAT('Week ', WEEK(dateposted)) AS week_label,
    COUNT(id) AS total_complaints,
    SUM(CASE WHEN status = '已解决' THEN 1 ELSE 0 END) AS resolved_complaints,
    SUM(CASE WHEN status = '待处理' THEN 1 ELSE 0 END) AS pending_complaints
FROM tblcomplain
GROUP BY post_year, post_week, week_label
ORDER BY post_year DESC, post_week DESC;

SQL Server版本 #

把日期函数换成SQL Server支持的DATEPART：

SELECT
    DATEPART(YEAR, dateposted) AS post_year,
    DATEPART(WEEK, dateposted) AS post_week,
    CONCAT('Week ', DATEPART(WEEK, dateposted)) AS week_label,
    COUNT(id) AS total_complaints,
    SUM(CASE WHEN status = '已解决' THEN 1 ELSE 0 END) AS resolved_complaints,
    SUM(CASE WHEN status = '待处理' THEN 1 ELSE 0 END) AS pending_complaints
FROM tblcomplain
GROUP BY DATEPART(YEAR, dateposted), DATEPART(WEEK, dateposted), week_label
ORDER BY post_year DESC, post_week DESC;

PostgreSQL版本 #

用EXTRACT函数提取年份和周数：

SELECT
    EXTRACT(YEAR FROM dateposted) AS post_year,
    EXTRACT(WEEK FROM dateposted) AS post_week,
    CONCAT('Week ', EXTRACT(WEEK FROM dateposted)) AS week_label,
    COUNT(id) AS total_complaints,
    SUM(CASE WHEN status = '已解决' THEN 1 ELSE 0 END) AS resolved_complaints,
    SUM(CASE WHEN status = '待处理' THEN 1 ELSE 0 END) AS pending_complaints
FROM tblcomplain
GROUP BY post_year, post_week, week_label
ORDER BY post_year DESC, post_week DESC;

如果需要把两种维度的数据放在同一张结果里（方便后续统一展示），可以用UNION ALL合并查询：

-- 合并月度和周度统计，添加维度标识区分
SELECT
    '月度' AS dimension_type,
    CONCAT(YEAR(dateposted), '-', MONTHNAME(dateposted)) AS period,
    COUNT(id) AS total_complaints,
    SUM(CASE WHEN status = '已解决' THEN 1 ELSE 0 END) AS resolved_complaints
FROM tblcomplain
GROUP BY YEAR(dateposted), MONTHNAME(dateposted)
UNION ALL
SELECT
    '周度' AS dimension_type,
    CONCAT(YEAR(dateposted), '-Week ', WEEK(dateposted)) AS period,
    COUNT(id) AS total_complaints,
    SUM(CASE WHEN status = '已解决' THEN 1 ELSE 0 END) AS resolved_complaints
FROM tblcomplain
GROUP BY YEAR(dateposted), WEEK(dateposted)
ORDER BY dimension_type, period DESC;

如果需要展示每周的具体起始和结束日期（比如“2024-05-06 至 2024-05-12”），以MySQL为例可以这么写：

-- MySQL 周度统计带日期范围（周一为一周开始）
SELECT
    YEAR(dateposted) AS post_year,
    WEEK(dateposted, 1) AS post_week,
    STR_TO_DATE(CONCAT(YEAR(dateposted), '-', WEEK(dateposted, 1), '-1'), '%X-%V-%w') AS week_start,
    STR_TO_DATE(CONCAT(YEAR(dateposted), '-', WEEK(dateposted, 1), '-7'), '%X-%V-%w') AS week_end,
    COUNT(id) AS total_complaints
FROM tblcomplain
GROUP BY post_year, post_week, week_start, week_end
ORDER BY post_year DESC, post_week DESC;

这些查询可以帮你快速从表中提取按时间维度聚合的数据，你可以根据实际业务需求调整统计字段（比如添加complain相关的统计）。如果是要在前端展示，把查询结果返回后用柱状图、折线图之类的可视化组件呈现，效果会更直观~

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