SQL查询需求：按周期统计动态团队的出现次数（含零值）

针对你提到的Team名称动态变更、需要统计1-13每个周期出现次数（无出现则为0）的需求，这里有个不需要硬编码Team的通用解决方案，完美适配PHP生成报表的场景：

核心思路 #

通过生成全量周期列表 + 关联所有唯一团队 + 左连接原表统计的方式，自动适配动态变化的Team，同时确保每个周期都有数据输出。

SQL查询实现 #

支持CTE的数据库（MySQL 8+/PostgreSQL/SQL Server等） #

WITH periods AS (
    -- 生成1到13的所有周期
    SELECT 1 AS period
    UNION ALL
    SELECT period + 1 FROM periods WHERE period < 13
),
all_teams AS (
    -- 获取表中所有唯一的Team
    SELECT DISTINCT Team FROM your_table_name -- 替换成你的实际表名
)
SELECT 
    at.Team,
    p.period,
    COUNT(t.id) AS occurrence_count -- 统计出现次数，无匹配则为0
FROM periods p
CROSS JOIN all_teams at -- 交叉连接得到所有Team-Period组合
LEFT JOIN your_table_name t 
    ON t.Team = at.Team 
    AND t.Period = p.period
GROUP BY at.Team, p.period
ORDER BY at.Team, p.period;

不支持CTE的老版本数据库（比如MySQL 5.x） #

把周期列表用UNION ALL直接生成即可：

SELECT 
    at.Team,
    p.period,
    COUNT(t.id) AS occurrence_count
FROM (
    SELECT 1 AS period 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 UNION ALL SELECT 13
) p
CROSS JOIN (SELECT DISTINCT Team FROM your_table_name) at
LEFT JOIN your_table_name t 
    ON t.Team = at.Team 
    AND t.Period = p.period
GROUP BY at.Team, p.period
ORDER BY at.Team, p.period;

PHP端转换为关联数组 #

拿到SQL查询结果后，可以轻松转换成适合报表的关联数组，示例代码（用PDO为例）：

// 假设$pdo是你的数据库连接实例
$sql = <<<SQL
WITH periods AS (
    SELECT 1 AS period
    UNION ALL
    SELECT period + 1 FROM periods WHERE period < 13
),
all_teams AS (
    SELECT DISTINCT Team FROM your_table_name
)
SELECT 
    at.Team,
    p.period,
    COUNT(t.id) AS occurrence_count
FROM periods p
CROSS JOIN all_teams at
LEFT JOIN your_table_name t 
    ON t.Team = at.Team 
    AND t.Period = p.period
GROUP BY at.Team, p.period
ORDER BY at.Team, p.period;
SQL;

$stmt = $pdo->query($sql);
$dbResult = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 构建以Team为键、周期为子键的关联数组
$reportData = [];
foreach ($dbResult as $row) {
    $team = $row['Team'];
    $period = (int)$row['period'];
    $count = (int)$row['occurrence_count'];
    
    if (!isset($reportData[$team])) {
        $reportData[$team] = [];
    }
    $reportData[$team][$period] = $count;
}

// 可选：给每个Team的周期按1-13排序，保证顺序一致
foreach ($reportData as &$teamPeriods) {
    ksort($teamPeriods);
}
unset($teamPeriods);

最终$reportData的结构大概是这样的：

[
    "TeamA" => [
        1 => 2,
        2 => 0,
        3 => 5,
        ...
        13 => 1
    ],
    "TeamB" => [
        1 => 0,
        2 => 3,
        ...
        13 => 0
    ]
]

为什么这个方案更好 #

• 完全适配动态Team：不需要手动修改SQL来新增/删除Team，自动从表中获取所有唯一团队
• 保证全周期覆盖：不管某个Team在某个周期有没有数据，都会输出对应的0值，完美满足报表需求
• 性能稳定：通过CROSS JOIN+LEFT JOIN的方式，避免了复杂的动态SQL拼接，安全性更高

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