MySQL按团队按季度统计关闭记录并转置列及PHP处理选择

仅用MySQL实现转置需求的方案 #

可以实现，核心思路是用条件聚合配合交叉连接生成完整的团队-年份维度，确保无数据的季度显示0。

假设你的数据表名为task_records，包含字段teamNumber（团队编号）、status（状态）、finish_time（记录完成时间，用于提取年份和季度），以下是完整查询示例：

SELECT
    t.teamNumber,
    y.year,
    -- 按季度统计数量，无数据时返回0
    SUM(CASE WHEN QUARTER(tr.finish_time) = 1 THEN 1 ELSE 0 END) AS q1,
    SUM(CASE WHEN QUARTER(tr.finish_time) = 2 THEN 1 ELSE 0 END) AS q2,
    SUM(CASE WHEN QUARTER(tr.finish_time) = 3 THEN 1 ELSE 0 END) AS q3,
    SUM(CASE WHEN QUARTER(tr.finish_time) = 4 THEN 1 ELSE 0 END) AS q4
FROM
    -- 生成所有有效团队的列表
    (SELECT DISTINCT teamNumber FROM task_records WHERE status = 'CLOSED') t
    -- 生成所有涉及的年份列表，若需固定年份范围可替换为UNION语句（如SELECT 2022 UNION SELECT 2023）
    CROSS JOIN (SELECT DISTINCT YEAR(finish_time) AS year FROM task_records WHERE status = 'CLOSED') y
    -- 左连接原始数据，确保每个团队-年份组合都有记录
    LEFT JOIN task_records tr 
        ON t.teamNumber = tr.teamNumber 
        AND YEAR(tr.finish_time) = y.year 
        AND tr.status = 'CLOSED'
GROUP BY
    t.teamNumber, y.year
ORDER BY
    t.teamNumber, y.year;

这段SQL通过交叉连接生成所有团队与年份的组合，再用CASE WHEN配合SUM实现列转置，无对应数据的季度会自动填充0。

MySQL处理 vs PHP处理的选择 #

在MySQL中处理的优势 #

• 直接得到结构化结果，PHP只需简单遍历渲染，减少后端代码复杂度
• 数据库擅长聚合计算，数据量较小时性能更优
• 结果格式固定，适合前端直接生成表格

在PHP中处理的优势 #

• 灵活性更高：后续若需调整展示维度（如增加季度、拆分月份），无需修改SQL，直接在PHP中调整逻辑
• 降低数据库负载：数据量大时，数据库聚合转置会消耗更多CPU资源，PHP处理可让数据库仅返回基础统计数据（团队、年份、季度、数量），再由PHP重组结构
• 适配动态场景：若需根据用户输入动态调整展示的年份或团队，PHP能更灵活地处理数据重组

总结建议 #

• 数据量小、展示格式固定：优先用MySQL处理，减少后端代码量
• 数据量大、需要灵活调整展示逻辑：先让MySQL返回teamNumber、year、quarter、count的基础统计结果，再用PHP重组为目标格式。示例PHP逻辑如下：

// 假设从数据库获取的原始统计数据
$rawStats = [
    ['teamNumber' => 'team1', 'year' => 2023, 'quarter' => 1, 'count' => 8],
    ['teamNumber' => 'team1', 'year' => 2023, 'quarter' => 3, 'count' => 5],
    // ...更多数据
];

$finalData = [];
foreach ($rawStats as $item) {
    $team = $item['teamNumber'];
    $year = $item['year'];
    $quarterKey = "q{$item['quarter']}";
    
    // 初始化团队-年份的季度结构，默认值为0
    if (!isset($finalData[$team][$year])) {
        $finalData[$team][$year] = ['q1' => 0, 'q2' => 0, 'q3' => 0, 'q4' => 0];
    }
    $finalData[$team][$year][$quarterKey] = $item['count'];
}

// 转换为平铺的展示数组
$displayData = [];
foreach ($finalData as $team => $yearData) {
    foreach ($yearData as $year => $quarters) {
        $displayData[] = array_merge(
            ['teamNumber' => $team, 'year' => $year],
            $quarters
        );
    }
}

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