本文介绍如何通过 cross join 与 left join 的组合，确保查询结果中每个城市-分类组合均被完整列出，并将无匹配组织的计数准确显示为 0（而非忽略 null）。
本文介绍如何通过 cross join 与 left join 的组合，确保查询结果中每个城市-分类组合均被完整列出，并将无匹配组织的计数准确显示为 0（而非忽略 null）。
在构建多维统计报表（如“每个城市下各行业机构数量”）时，一个常见痛点是：默认 JOIN 会跳过无匹配记录，导致缺失行（如纽约的 psychologist 显示为空，而非 psychologist: 0）。根本原因在于，当某城市下某分类没有任何组织时，该组合在 orgs 表中不存在对应行，普通 LEFT JOIN 若条件写在 WHERE 子句中还会意外过滤掉 NULL，造成数据丢失。
正确解法是 先生成完整的城市 × 分类笛卡尔积，再关联组织表进行计数。以下是推荐的标准化 SQL：
SELECT c.name AS city, cat.name AS category, COUNT(o.id) AS org_number FROM cities c CROSS JOIN categories cat LEFT JOIN orgs o ON o.city_id = c.id AND o.category_id = cat.id GROUP BY c.id, c.name, cat.id, cat.name ORDER BY c.id, cat.id;
✅ 关键要点说明：
-
CROSS JOIN categories强制生成所有城市与分类的组合（共 3×3=9 行），确保无遗漏； -
LEFT JOIN orgs使用复合连接条件ON o.city_id = c.id AND o.category_id = cat.id，精准匹配组织归属，不依赖 WHERE 过滤； -
COUNT(o.id)自动将无匹配的o.id计为0（因COUNT()忽略 NULL，而o.id在未匹配时为 NULL）； -
GROUP BY必须包含c.id,cat.id（主键）及对应名称字段，避免非确定性错误（尤其在严格 SQL 模式下）。
⚠️ 常见错误规避：
- ❌ 错误写法：
LEFT JOIN ... WHERE o.city_id = ?—— 此时WHERE会将o.city_id IS NULL的行过滤掉，等效于内连接； - ❌ 使用
COUNT(*)或COUNT(1)—— 它们会统计所有分组行（包括 NULL 匹配行），导致结果恒为 1； - ❌ 省略
cat.id在GROUP BY中 —— 可能引发ONLY_FULL_GROUP_BY报错或不可靠聚合。
最终输出格式可直接用于模板渲染（如 Markdown 或 HTML）：
**London** doctor: 0 lawyer: 1 psychologist: 0 **New York** doctor: 1 lawyer: 0 psychologist: 0 **Berlin** doctor: 0 lawyer: 0 psychologist: 1
此方案兼容 MySQL 5.7+、PostgreSQL、SQL Server 和 SQLite（需 3.25+ 支持 CROSS JOIN），是处理“稀疏维度全量统计”的通用范式。
