PDO MySQL查询结果格式化适配JavaScript及区域周数据统计需求

嘿，我太懂这种反复折腾查询和数组拼接的痛苦了——之前做区域周报统计的时候，也傻乎乎跑了好几次查询再手动合并数据，结果不仅慢还容易出错！针对你的需求，其实只用一次高效的SQL查询就能搞定所有数据，完全不用折腾数组遍历，直接给前端喂数据生成柱状图就行。

核心解决方案：一次SQL聚合搞定周+区域统计 #

先确认你的表结构：id（主键）、region（区域标识，比如你提到的W/其他两个区域）、date（记录日期）。下面分常用数据库给出具体方案：

1. MySQL/MariaDB 版本 #

用DATE_FORMAT把日期转成标准的「年-周」格式，再通过条件聚合把每个区域的记录数转成单独列，输出结构直接适配柱状图需求：

SELECT
  DATE_FORMAT(date, '%X-%V') AS week, -- 输出格式示例：2024-W01（ISO标准周）
  COUNT(CASE WHEN region = 'W' THEN 1 END) AS west_count,
  COUNT(CASE WHEN region = 'E' THEN 1 END) AS east_count,
  COUNT(CASE WHEN region = 'S' THEN 1 END) AS south_count
FROM your_table_name
GROUP BY week
ORDER BY week;

2. PostgreSQL 版本 #

用DATE_TRUNC截断日期到周，再用同样的条件聚合逻辑：

SELECT
  TO_CHAR(DATE_TRUNC('week', date), 'IYYY-IW') AS week, -- ISO标准年-周格式
  COUNT(CASE WHEN region = 'W' THEN 1 END) AS west_count,
  COUNT(CASE WHEN region = 'E' THEN 1 END) AS east_count,
  COUNT(CASE WHEN region = 'S' THEN 1 END) AS south_count
FROM your_table_name
GROUP BY DATE_TRUNC('week', date)
ORDER BY DATE_TRUNC('week', date);

为什么这比你之前的方法好？ #

• 减少数据库交互：不用跑4次查询再手动合并，一次查询拿到所有核心数据，降低数据库负载和网络开销
• 直接适配前端需求：输出的周->各区域数量结构，前端拿到后可以直接绑定到柱状图组件（比如ECharts、Highcharts），完全不用遍历数组拼接
• 自动处理空值：如果某周某个区域没有记录，COUNT(CASE...)会返回0，柱状图上会自然显示空值，不用额外补数据

进阶技巧：补全空白周 #

如果某周所有区域都没有记录，上面的查询会跳过该周。如果需要在柱状图上显示这些空白周（数据为0），可以先生成连续的周序列，再左连接你的统计数据：

-- MySQL示例：生成最近12周的连续序列
WITH RECURSIVE weeks AS (
  SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 7*11 DAY) AS week_start
  UNION ALL
  SELECT DATE_ADD(week_start, INTERVAL 7 DAY) FROM weeks WHERE week_start < CURDATE()
)
SELECT
  DATE_FORMAT(w.week_start, '%X-%V') AS week,
  COUNT(CASE WHEN t.region = 'W' THEN 1 END) AS west_count,
  COUNT(CASE WHEN t.region = 'E' THEN 1 END) AS east_count,
  COUNT(CASE WHEN t.region = 'S' THEN 1 END) AS south_count
FROM weeks w
LEFT JOIN your_table_name t ON DATE_FORMAT(t.date, '%X-%V') = DATE_FORMAT(w.week_start, '%X-%V')
GROUP BY week
ORDER BY week;

前端快速上手示例 #

假设后端返回的数据格式是[{week: '2024-W01', west_count:5, east_count:8, south_count:3}, ...]，用ECharts生成柱状图的代码可以这么写：

const chartData = 后端返回的数组;
const xAxisData = chartData.map(item => item.week);
const series = [
  { name: '西部', type: 'bar', data: chartData.map(item => item.west_count) },
  { name: '东部', type: 'bar', data: chartData.map(item => item.east_count) },
  { name: '南部', type: 'bar', data: chartData.map(item => item.south_count) }
];

// 初始化ECharts实例
const myChart = echarts.init(document.getElementById('chart-container'));
myChart.setOption({
  xAxis: { type: 'category', data: xAxisData },
  yAxis: { type: 'value', name: '记录行数' },
  series: series
});

我之前就是用这个思路解决了几乎一样的报表需求，亲测比四次查询+数组遍历高效太多，还不容易出错！

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