MySQL 按商品最新库存为0条件过滤对应全部行的实现方法
按商品维度整组过滤数据行的SQL实现方案
原测试语句的问题定位
你写的测试SQL存在三处核心问题,无法实现预期效果:
- 语法错误:字符串类型的
R0001没有加单引号,执行时会直接触发解析报错 - CASE表达式逻辑残缺:没有覆盖
remains=0的判断分支,表达式返回值没有匹配WHERE条件的判断逻辑 - 逻辑偏差:过滤条件是逐行判断的,没有基于
ProdNo做同组聚合校验,只能实现单条记录的过滤,无法联动隐藏同一商品的所有关联行
核心业务逻辑拆解
你的过滤规则本质是按ProdNo维度做整组校验:
对同一个商品编号下的所有库存记录,先找到该商品最新的库存记录(对应表中最大ID值,因为ID是自增主键,ID越大记录越新):
- 如果最新记录的
remains值为0,判定该商品已清库存,隐藏该商品对应的所有历史行- 如果最新记录的
remains值大于0,保留该商品的所有历史库存行
可落地的SQL实现
根据数据库对窗口函数的支持程度,可以选择两种兼容性不同的写法:
通用兼容版(支持所有SQL数据库版本,无版本要求)
采用EXISTS关联判断的方式,不需要复杂的窗口函数,执行效率也更稳定:
SELECT * FROM 库存表 t WHERE EXISTS ( -- 判断当前行所属的商品,是否满足最新库存大于0的条件 SELECT 1 FROM 库存表 t_sub WHERE t_sub.ProdNo = t.ProdNo -- 定位到该商品的最新库存记录 AND t_sub.ID = ( SELECT MAX(ID) FROM 库存表 t_max WHERE t_max.ProdNo = t.ProdNo ) -- *最新库存大于0才保留* AND t_sub.remains > 0 )
注意:代码中的库存表需要替换为你实际使用的表名。
窗口函数版(支持MySQL8.0+、PostgreSQL、SQL Server等现代数据库)
写法更简洁,逻辑更直观:
WITH 分组行号标记 AS ( SELECT *, -- 按商品分组,给最新的库存记录标记为行号1 ROW_NUMBER() OVER ( PARTITION BY ProdNo ORDER BY ID DESC ) AS rn FROM 库存表 ), 有效商品清单 AS ( -- 筛选出最新库存大于0的商品编号 SELECT ProdNo FROM 分组行号标记 WHERE rn = 1 AND remains > 0 ) SELECT g.ID, g.ProdNo, g.remains FROM 库存表 g INNER JOIN 有效商品清单 e ON g.ProdNo = e.ProdNo;
结果校验
- 当R0001的最新记录(ID=3)
remains=0时,该商品不会进入有效商品清单,所有关联行都会被过滤,最终输出仅包含R0002的库存行,匹配第一版预期结果 - 当R0001的最新记录(ID=3)
remains=30时,该商品会进入有效商品清单,所有关联的历史库存行都会正常展示,匹配第二版预期结果
内容的提问来源于stack exchange,提问作者Bryan Caliwag
相关产品推荐
相关产品推荐

