本文介绍如何通过 SQL 的 GROUP BY 与 CASE WHEN 结合左连接，将多对一关系中的站点数量（0、1、≥2）智能映射为“No site”“单地址”或“Multiple Sites”，避免项目号重复输出。

本文介绍如何通过 sql 的 `group by` 与 `case when` 结合左连接，将多对一关系中的站点数量（0、1、≥2）智能映射为“no site”“单地址”或“multiple sites”，避免项目号重复输出。

在处理 projects 与 sites 表的一对多关系时，直接使用 LEFT JOIN 会导致一个项目对应多个站点时重复输出项目行（如 job3 出现两次），而业务需求要求每个项目仅占一行，并根据关联站点数量统一展示为三种状态之一：唯一地址、Multiple Sites 或 No site。核心解法不是在 PHP 层循环判断，而是将聚合逻辑下推至 SQL 层——利用 GROUP BY projects.id 对每个项目分组，并用 COUNT(*) 统计其关联站点数，再通过 CASE 表达式完成语义化映射。

以下是优化后的完整 SQL 查询：

SELECT 
    projects.project_number,
    CASE
        WHEN COUNT(sites.id) > 1 THEN 'Multiple Sites'
        WHEN COUNT(sites.id) = 1 THEN sites.address
        ELSE 'No site'
    END AS site_address
FROM projects
LEFT JOIN sites ON projects.id = sites.project_fk
GROUP BY projects.id, projects.project_number;

✅ 关键说明：

• COUNT(sites.id) 比 COUNT(*) 更严谨：LEFT JOIN 中无匹配站点时 sites.id 为 NULL，COUNT() 自动忽略 NULL，确保 COUNT(sites.id) = 0 准确表示“无站点”；
• GROUP BY 必须包含 projects.id（主键）和 projects.project_number（非聚合字段），符合 SQL 标准（尤其在 ONLY_FULL_GROUP_BY 模式启用时）；
• sites.address 在 COUNT = 1 分支中可安全引用，因为该分组内仅存在唯一非空值（MySQL 5.7+ 严格模式下需确保此逻辑成立，否则建议改用 MAX(sites.address) 做兜底）。

PHP 部分同步精简，消除冗余判断逻辑：

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

立即学习“PHP免费学习笔记（深入）”；

<?php
$query = mysqli_query($conn, "
    SELECT 
        projects.project_number,
        CASE
            WHEN COUNT(sites.id) > 1 THEN 'Multiple Sites'
            WHEN COUNT(sites.id) = 1 THEN sites.address
            ELSE 'No site'
        END AS site_address
    FROM projects
    LEFT JOIN sites ON projects.id = sites.project_fk
    GROUP BY projects.id, projects.project_number
");

if (!$query) {
    die("Query failed: " . mysqli_error($conn));
}
?>
<div class="container">
    <table class="table">
        <thead>
            <tr>
                <th scope="col">Project Number</th>
                <th scope="col">Site</th>
            </tr>
        </thead>
        <tbody>
            <?php while ($row = mysqli_fetch_assoc($query)): ?>
                <tr>
                    <td><?= htmlspecialchars($row['project_number']) ?></td>
                    <td><?= htmlspecialchars($row['site_address']) ?></td>
                </tr>
            <?php endwhile; ?>
        </tbody>
    </table>
</div>

⚠️ 注意事项：

• 务必对输出内容使用 htmlspecialchars() 防止 XSS（原代码中未转义，存在安全风险）；
• 若 MySQL 版本 ≥ 8.0，可进一步用 COALESCE(MAX(sites.address), 'No site') 替代 CASE，但语义清晰度略低；
• 避免在 PHP 中先取全部数据再用 array_count_values() 等函数二次处理——既增加内存开销，又丧失数据库聚合的性能优势。

此方案以声明式 SQL 实现业务规则，结构清晰、执行高效，是处理“一对多聚合展示”类需求的标准实践。