MySQL 5.6三表关联统计含零值的各职位各状态招聘offer数

可以通过单条SQL查询实现该需求，核心逻辑是先生成职位和状态的全量维度组合，再关联招聘offer表做聚合统计。

原有查询的问题 #

你之前的查询以job_offer为驱动表做左连接，job_offer本身不存在的职位+状态组合不会出现在结果集中，因此只能统计到有实际offer的组合。

正确查询语句 #

SELECT 
    p.name AS position_name,
    s.name AS status_name,
    COUNT(jo.id) AS offers
FROM
    position p
CROSS JOIN
    status s
LEFT JOIN
    job_offer jo
        ON jo.position_id = p.id 
        AND jo.status_id = s.id
GROUP BY
    p.name, s.name
ORDER BY
    p.id, s.id

逻辑说明 #

• 首先通过CROSS JOIN对position和status表做笛卡尔积，得到2个职位×4个状态共8行全量维度组合，覆盖你需要的所有统计维度
• 再通过LEFT JOIN关联job_offer表，关联条件同时匹配职位ID和状态ID，没有对应招聘offer的组合关联结果为NULL
• 最后用COUNT(jo.id)统计，NULL值不会被计数，无对应offer的组合统计结果自动为0
• 可选增加ORDER BY子句保证结果排序和你预期的一致

你可以直接在MySQL 5.6环境执行上述语句，返回结果和你给出的预期结果完全一致。

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