MySQL单查询实现筛选最新状态为1或无状态记录的商品

AI 小北

2026-8-27

MySQL单条查询实现商品状态筛选需求

现有表结构

products 商品表：字段为id（商品ID）、productName（商品名称），示例数据如下：
```
id, productName
1, Orange
2, Apple
3, Lemon
4, Cherry
```
productStatus 商品状态表：字段为id（记录ID）、productId（关联商品ID）、status（状态值）、date（记录生成时间），示例数据如下：
```
id, productId, status, date
1, 1, 1, 2022-07-11 07:00:00
2, 1, 3, 2022-07-11 08:00:00
3, 1, 5, 2022-07-11 09:00:00
4, 3, 1, 2022-07-11 07:00:00
```

筛选规则

需要返回满足任意一个条件的商品：

商品最新的状态记录（按date字段倒序取第一条）的status值为1
商品在productStatus表中无任何对应记录

单条查询实现方案

完全可以通过单条SQL实现，替代原有循环嵌套的N+1查询，性能提升明显，以下分版本提供写法：

MySQL 8.0及以上版本（支持窗口函数，推荐）

利用ROW_NUMBER()窗口函数直接给每个商品的状态记录按时间倒序编号，取每个商品编号为1的最新记录做左连接筛选：

SELECT p.*
FROM products p
LEFT JOIN (
    SELECT 
        productId,
        status,
        ROW_NUMBER() OVER (PARTITION BY productId ORDER BY `date` DESC) AS rn
    FROM productStatus
) ps ON p.id = ps.productId AND ps.rn = 1
WHERE ps.status = 1 OR ps.productId IS NULL;

MySQL 5.x版本（不支持窗口函数）

通过关联子查询匹配每个商品的最大时间（即最新记录时间）关联状态表，再做筛选：

SELECT p.*
FROM products p
LEFT JOIN productStatus ps
  ON p.id = ps.productId
  AND ps.`date` = (
      SELECT MAX(`date`)
      FROM productStatus
      WHERE productId = p.id
  )
WHERE ps.status = 1 OR ps.productId IS NULL;