MySQL查询：筛选同时包含status=0及特定status值的order_id

针对数据量大的订单表，要筛选出同时存在status=0工单和status属于(2,16,20)工单的order_id，以下是几种高效实现方案：

方案一：分组聚合筛选 #

先过滤无关状态的记录，再按订单分组，通过聚合函数判断分组内是否同时满足两种状态条件：

SELECT order_id
FROM your_order_table
WHERE status IN (0, 2, 16, 20) -- 过滤掉无需处理的状态，减少计算量
GROUP BY order_id
HAVING SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN status IN (2,16,20) THEN 1 ELSE 0 END) > 0;

性能优化点：在(order_id, status)上建立联合索引，可实现覆盖查询，无需回表读取额外数据，大幅提升查询速度。

方案二：内连接取交集 #

分别筛选出包含两种目标状态的订单集合，通过内连接取交集：

SELECT DISTINCT t1.order_id
FROM your_order_table t1
JOIN your_order_table t2 
  ON t1.order_id = t2.order_id
WHERE t1.status = 0
  AND t2.status IN (2,16,20);

适用场景：若status字段已有单独索引，该方案能快速定位对应记录；数据量极大时，可将DISTINCT替换为GROUP BY t1.order_id，效果一致。

方案三：EXISTS子查询 #

先定位包含status=0的订单，再通过子查询验证该订单是否存在目标状态的工单：

SELECT DISTINCT order_id
FROM your_order_table t1
WHERE t1.status = 0
  AND EXISTS (
    SELECT 1
    FROM your_order_table t2
    WHERE t2.order_id = t1.order_id
      AND t2.status IN (2,16,20)
  );

优势：子查询找到符合条件的记录后立即停止遍历，在部分场景下比聚合查询更高效；同样依赖(order_id, status)联合索引优化性能。

通用性能建议 #

• 优先建立(order_id, status)联合索引，避免全表扫描，这是大数据量场景下的核心优化手段。
• 始终通过WHERE子句提前过滤无关数据，减少后续计算的数据集规模。
• 仅查询必要字段（如仅order_id），避免不必要的IO开销。

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