MySQL添加ORDER BY后累加和WHERE条件失效问题求助

你遇到的这个问题，本质是没搞清楚MySQL的查询执行顺序和用户变量的工作逻辑：

MySQL执行SQL的流程是先过滤WHERE条件→再执行ORDER BY排序→最后处理SELECT里的字段计算。你原来的语句里，把@total < 100000写在WHERE里，可WHERE执行的时候，@total的累加操作还没开始呢！等WHERE把所有符合storage_id=4、id<1000、weight>0的记录都筛选出来，才会开始逐行计算累加值，这时候@total自然会一路加到超过100000，完全不受WHERE里的限制。

至于你说移除某些条件后“正常”，那纯属巧合——当没有ORDER BY或者特定过滤条件时，MySQL的执行计划刚好是边读行边计算变量，这时候WHERE里的判断碰巧生效，但这种行为完全依赖MySQL的内部优化，哪天数据量变了、加了索引，结果就会乱掉，根本不可靠。

根据你的MySQL版本，有两种稳定的实现方式：

如果你用的是MySQL 8.0及以上（强烈推荐） #

用窗口函数就完事了，这是标准SQL特性，行为稳定还好维护：

SELECT id, storage_id, weight, c_sum
FROM (
    SELECT 
        id, 
        storage_id, 
        weight,
        -- 按id倒序计算累加和，从第一条到当前行的总和
        SUM(weight) OVER (ORDER BY id DESC) AS c_sum
    FROM storage_movements
    WHERE weight > 0 AND storage_id = 4 AND id < 1000
) AS sub
-- 这里的逻辑是：保留累加和未超过100000的记录；如果想包含最后一条让总和刚好超过阈值的记录，就改成c_sum - weight < 100000
WHERE c_sum <= 100000;

简单说就是先把符合条件的记录筛选出来排好序，再计算每行的累加和，最后只留下累加和没超阈值的那些。

如果是MySQL 5.x版本（兼容旧版本） #

得用用户变量，但必须先把过滤和排序的结果做成临时表，再逐行累加过滤：

SELECT id, storage_id, weight, @total := @total + weight AS c_sum
FROM (
    -- 先把所有符合条件的记录筛选出来，按id倒序排好
    SELECT id, storage_id, weight
    FROM storage_movements
    WHERE weight > 0 AND storage_id = 4 AND id < 1000
    ORDER BY id DESC
) AS filtered_records
-- 初始化累加变量
JOIN (SELECT @total := 0) AS init
-- 判断当前行加进去后会不会超阈值，不超才返回
WHERE @total + weight <= 100000;

这个逻辑是先拿到排序好的目标记录，再从0开始逐行累加，每加一行前先判断加完会不会超100000，不会的话就加入结果集，直到累加后超过阈值就停止。

这两种方法都能稳定满足你的需求，再也不会出现那种“去掉某个条件就好，加上就坏”的诡异情况了。

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