MySQL查询多次Shipped且Returned后有Shipped的item_id

筛选逻辑说明 #

需同时满足两个校验规则，所有时间判断严格基于created_at字段值，与记录的物理存储顺序无关：

• 单个item_id关联的status="Shipped"记录总数不少于2条
• 单个item_id存在至少1条status="Returned"记录，且至少有1条Shipped记录的创建时间晚于某条Returned记录的创建时间

你原有写法仅能实现第一条规则的筛选，且依赖表存在id主键（给出的表结构中未声明该字段），通用性不足，也未覆盖时间先后校验逻辑。

推荐SQL实现 #

高性能聚合写法（首选） #

仅需一次表扫描分组即可完成所有校验，查询效率最高：

SELECT item_id
FROM item_status_history
GROUP BY item_id
HAVING
  -- 校验Shipped记录数不少于2条
  SUM(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END) >= 2
  -- 校验存在Returned记录
  AND SUM(CASE WHEN status = 'Returned' THEN 1 ELSE 0 END) >= 1
  -- 校验存在Returned之后生成的Shipped记录：最晚的Shipped时间晚于最早的Returned时间时，该条件必然成立
  AND MAX(CASE WHEN status = 'Shipped' THEN created_at END) > MIN(CASE WHEN status = 'Returned' THEN created_at END)

该写法会自动过滤不符合场景：如果同item下所有Shipped记录都生成于Returned之前，那么最晚的Shipped时间必然早于最早的Returned时间，无法通过HAVING校验。

分步直观写法 #

如果需要更清晰的分步逻辑方便后续规则调整，可以使用嵌套查询+EXISTS判断的版本：

SELECT t1.item_id
FROM (
  -- 第一步：筛出Shipped记录数>=2的item_id
  SELECT item_id
  FROM item_status_history
  WHERE status = 'Shipped'
  GROUP BY item_id
  HAVING COUNT(*) >= 2
) t1
WHERE EXISTS (
  -- 第二步：匹配存在Returned在前、Shipped在后的记录组合
  SELECT 1
  FROM item_status_history r
  JOIN item_status_history s 
    ON r.item_id = s.item_id
    AND s.status = 'Shipped'
    AND s.created_at > r.created_at
  WHERE r.status = 'Returned'
    AND r.item_id = t1.item_id
)

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