本文介绍如何通过 cross join 与 left join 结合，生成所有城市×分类的笛卡尔积，并准确统计每个城市下各分类的组织数量（包括为0的情况），避免 null 值被过滤导致缺失行。
本文介绍如何通过 cross join 与 left join 结合，生成所有城市×分类的笛卡尔积，并准确统计每个城市下各分类的组织数量（包括为0的情况），避免 null 值被过滤导致缺失行。
在实际业务报表中，我们常需展示“全维度覆盖”的统计结果——例如：每个城市下所有职业分类（医生、律师、心理师）对应的机构数量，即使某城市当前没有某类机构，也应显示 0 而非直接省略该行。原始查询使用 LEFT JOIN orgs ON ... WHERE o.city_id = ? 的方式会因 WHERE 子句将 NULL 行（即无匹配组织的记录）过滤掉，导致零值缺失。
正确解法是先构建完整的城市–分类组合，再关联组织表进行计数。核心思路如下：
-
用
CROSS JOIN生成所有城市与分类的笛卡尔积（即每个城市配对每个分类）； -
用
LEFT JOIN关联orgs表，并把关联条件（city_id和category_id）写在ON子句中（而非WHERE），确保未匹配的组合仍保留； -
用
COUNT(o.city_id)统计（注意：COUNT(列)自动忽略NULL，恰好将无匹配组织的行计为0）； - 按城市 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...），真正实现“零值可见、维度完整、逻辑健壮”。
