MySQL如何查询累计值超过指定阈值的较早记录用于删除

我们先假设你的表名为file_records，可替换为你实际使用的表名。

版本1：MySQL 8.0+（支持窗口函数，推荐写法） #

用窗口函数实现倒序累计求和，写法简洁易读：

SELECT Id, FileSizeMB, cumulative_size
FROM (
    SELECT 
        Id,
        FileSizeMB,
        SUM(FileSizeMB) OVER (ORDER BY Id DESC) AS cumulative_size
    FROM file_records
) t
WHERE cumulative_size >= 500;

如果需要直接执行删除操作，可以用以下语句：

DELETE FROM file_records
WHERE Id IN (
    SELECT Id FROM (
        SELECT 
            Id,
            SUM(FileSizeMB) OVER (ORDER BY Id DESC) AS cumulative_size
        FROM file_records
    ) t
    WHERE cumulative_size >= 500
);

版本2：MySQL 5.x（不支持窗口函数，用用户变量实现） #

如果你的MySQL版本较低不支持窗口函数，可以用自定义变量计算累计值：

SELECT Id, FileSizeMB, cumulative_size
FROM (
    SELECT 
        Id,
        FileSizeMB,
        @cumulative := @cumulative + FileSizeMB AS cumulative_size
    FROM file_records
    ORDER BY Id DESC
) t, (SELECT @cumulative := 0) init
WHERE cumulative_size >= 500;

对应删除语句如下：

DELETE FROM file_records
WHERE Id IN (
    SELECT Id FROM (
        SELECT 
            Id,
            @cumulative := @cumulative + FileSizeMB AS cumulative_size
        FROM file_records
        ORDER BY Id DESC
    ) t, (SELECT @cumulative := 0) init
    WHERE cumulative_size >= 500
);

*注意：执行删除操作前，请先运行对应的查询语句校验返回结果是否符合预期，避免误删数据。

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