三表关联下各月份处理全量订单的最早Job单SQL实现
每月覆盖全量订单的最小ID Job查询实现方案
之前用普通GROUP BY查询始终无法得到正确结果,核心原因是没有将「当月总订单规模」作为固定校验基数,直接分组取值无法保证Job真的覆盖了当月全部订单。本次实现严格遵循业务规则设计,所有校验逻辑可精准保障匹配关系正确:
- 单月订单统计仅以Order表的有效记录为基准,如需过滤无效订单可直接在聚合条件中追加WHERE子句
- 单个Job的当月覆盖订单数仅统计其通过JobOrder关联的当月订单,跨月份关联的订单不会计入当月覆盖数,完全匹配「单个Job可处理跨多月订单」的规则
- 核心校验条件:Job的当月覆盖订单数必须等于当月总订单数,无任何遗漏
- 最终对每个符合条件的月份,取满足要求的最小Job.id即为结果
单条SQL实现
WITH month_total_orders AS ( -- 第一步:统计每个月的订单总数量 SELECT month, COUNT(DISTINCT id) AS total_order_cnt FROM `Order` -- 若数据库存在保留字冲突,可按语法加对应转义符,如PostgreSQL使用"Order" -- 如需过滤无效订单,可在此处加WHERE条件,例如WHERE is_valid = 1 GROUP BY month ), job_month_covered_orders AS ( -- 第二步:统计每个月下,每个Job关联的当月订单数量 SELECT o.month, j.id AS job_id, COUNT(DISTINCT o.id) AS covered_order_cnt FROM JobOrder jo INNER JOIN `Order` o ON jo.order_id = o.id -- 关联Job表,如需过滤Job状态(例如仅取生效中的Job),可在此处加条件,例如AND j.status = '生效中' INNER JOIN Job j ON jo.job_id = j.id GROUP BY o.month, j.id ) -- 第三步:关联筛选出覆盖全月订单的Job,取每个月最小的Job.id SELECT mto.month, MIN(jmco.job_id) AS eligible_job_id FROM month_total_orders mto INNER JOIN job_month_covered_orders jmco ON mto.month = jmco.month -- 核心校验:Job覆盖的订单数等于当月总订单数 AND mto.total_order_cnt = jmco.covered_order_cnt GROUP BY mto.month;
补充说明:
- 以上SQL适配MySQL 8.0+、PostgreSQL、SQL Server等支持CTE的主流数据库,如需兼容低版本MySQL,可将两个CTE替换为对应子查询即可。
- 如需返回没有符合条件Job的月份(对应Job.id为NULL),可将第三步的
INNER JOIN修改为LEFT JOIN即可。- 若JobOrder表不存在重复关联同一订单的情况,统计时的
DISTINCT可以移除,进一步提升查询性能。
内容的提问来源于stack exchange,提问作者Adam
相关产品推荐
相关产品推荐

