如何合并SQL查询以按日统计基于不同字段的活跃与非活跃职位空缺数量

嘿，我来帮你把这两个独立的查询合并成一个，直接得到包含每日活跃和非活跃职位数量的结果集！

思路分析 #

你的两个查询分别基于published_date统计活跃职位、基于updated统计非活跃职位，核心都是按日历日期分组。我们可以把这两个统计逻辑拆成子查询，然后以calendar表为基础，通过日期字段关联两个子查询的结果，确保所有目标日期都被覆盖，同时把空值转为0（避免某天没有数据时显示NULL）。

兼容MySQL的最终查询 #

因为MySQL不支持FULL OUTER JOIN，所以我们用calendar作为主表，左连接两个统计子查询，保证所有目标日期都出现在结果里：

SELECT 
    c.datefield AS created,
    COALESCE(a.amount_active_vacancies, 0) AS amount_active_vacancies,
    COALESCE(i.amount_inactive_vacancies, 0) AS amount_inactive_vacancies
FROM calendar AS c
LEFT JOIN
    (SELECT 
        DATE(v.published_date) AS stat_date,
        SUM(CASE WHEN v.vacancy_status_id = 2 THEN 1 ELSE 0 END) AS amount_active_vacancies
     FROM vacancy AS v
     GROUP BY DATE(v.published_date)) AS a 
    ON c.datefield = a.stat_date
LEFT JOIN
    (SELECT 
        DATE(v.updated) AS stat_date,
        SUM(CASE WHEN v.vacancy_status_id IN (3, 4) THEN 1 ELSE 0 END) AS amount_inactive_vacancies
     FROM vacancy AS v
     GROUP BY DATE(v.updated)) AS i 
    ON c.datefield = i.stat_date
WHERE 
    c.datefield BETWEEN (SELECT MIN(DATE(created)) FROM vacancy) 
    AND DATE(NOW() - INTERVAL 1 DAY)
ORDER BY c.datefield;

代码说明 #

1. 子查询a：专门统计每日通过published_date变为活跃（状态2）的职位数量，按日期分组。
2. 子查询i：统计每日通过updated变为非活跃（状态3或4）的职位数量，同样按日期分组。
3. 主查询：以calendar表的日期为基准，左连接两个子查询，用COALESCE把没有匹配数据的统计值转为0，最后过滤出目标日期范围并排序。

如果你的数据库支持FULL OUTER JOIN（比如PostgreSQL），也可以用两个子查询直接做全连接，但上面的方案更通用，适配MySQL环境。

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