本文介绍如何通过 cross join 与 left join 结合，生成所有城市×分类的笛卡尔积，并准确统计每个城市下各分类的组织数量（包括为0的情况），避免 null 值被过滤导致缺失行。

本文介绍如何通过 cross join 与 left join 结合，生成所有城市×分类的笛卡尔积，并准确统计每个城市下各分类的组织数量（包括为0的情况），避免 null 值被过滤导致缺失行。

在实际业务报表中，我们常需展示“全维度覆盖”的统计结果——例如：每个城市下所有职业分类（医生、律师、心理师）对应的机构数量，即使某城市当前没有某类机构，也应显示 0 而非直接省略该行。原始查询使用 LEFT JOIN orgs ON ... WHERE o.city_id = ? 的方式会因 WHERE 子句将 NULL 行（即无匹配组织的记录）过滤掉，导致零值缺失。

正确解法是先构建完整的城市–分类组合，再关联组织表进行计数。核心思路如下：

1. 用 CROSS JOIN 生成所有城市与分类的笛卡尔积（即每个城市配对每个分类）；
2. 用 LEFT JOIN 关联 orgs 表，并把关联条件（city_id 和 category_id）写在 ON 子句中（而非 WHERE），确保未匹配的组合仍保留；
3. 用 COUNT(o.city_id) 统计（注意：COUNT(列) 自动忽略 NULL，恰好将无匹配组织的行计为 0）；
4. 按城市 ID 和分类 ID 分组并排序，保证输出结构清晰、可读性强。

以下是完整可执行的 SQL 示例：

SELECT 
  c.name AS city,
  cat.name AS category,
  COUNT(o.id) AS org_count
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 确保即使某城市尚无任何机构，其下所有分类仍会出现在结果中；
• LEFT JOIN ... ON 中同时限定 city_id 和 category_id，精准匹配组织归属；
• 使用 COUNT(o.id)（而非 COUNT(*)）可正确返回 0——因为当 o.id 为 NULL 时，COUNT 会跳过该行；若误用 COUNT(*)，则每组都会返回 1（因 CROSS JOIN 已生成一行）；
• GROUP BY 中显式包含 c.name 和 cat.name（而不仅 ID），既满足标准 SQL 要求，也提升可读性与兼容性（如 PostgreSQL 严格模式）。

执行后，您将得到结构化输出，例如：

city      | category     | org_count
----------|--------------|----------
london    | doctor       | 0
london    | lawyer       | 1
london    | psychologist | 0
new york  | doctor       | 1
new york  | lawyer       | 0
new york  | psychologist | 0
berlin    | doctor       | 0
berlin    | lawyer       | 0
berlin    | psychologist | 1

后续在应用层（如 Python/PHP/前端）遍历时，即可按城市分组渲染为所需格式（如 **London**\ndoctor: 0\nlawyer: 1\n...），真正实现“零值可见、维度完整、逻辑健壮”。