mysql - SQL查询获取行直到列的总和等于某个值

Question

我的架构:

create table Warehouse
(
    ware_id    int,
    weight     int unsigned,
    added_date date
);

insert into Warehouse
values (56, 21, '2008-04-04'),
       (42, 40, '2009-10-13'),
       (10, 11, '2010-11-14'),
       (22, 2, '2010-11-22'),
       (19, 27, '2012-01-25'),
       (80, 14, '2012-01-25'),
       (21, 31, '2012-03-11'),
       (52, 32, '2012-05-30'),
       (49, 4, '2013-02-26'),
       (77, 17, '2013-05-24'),
       (22, 54, '2016-04-18'),
       (40, 19, '2017-02-20');

我有一个仓库，里面存放了很多有重量限制的商品。

所有商品的总和必须小于等于272。

如果要添加一个新的商品，而仓库容量已满，我们必须移除最旧的商品，以便为新的商品腾出可用空间，这个过程将一直持续到仓库空闲空间等于或大于新货重量。

我如何做一个简单的查询来返回所有需要删除的商品？

例如，如果新商品的权重为 100，则查询应返回如下内容:

| ware_id | weight | added_date |
|---------|--------|------------|
|      56 |     21 | 2008-04-04 |
|      42 |     40 | 2009-10-13 |
|      10 |     11 | 2010-11-14 |
|      22 |      2 | 2010-11-22 |
|      19 |     27 | 2012-01-25 |

SQL Fiddle

Answer 1

如果你运行的是 MySQL 8.0，你可以做一个窗口求和:

select *
from (
    select
        w.*,
        sum(weight) over(order by added_date desc, ware_id) running_weight
    from Warehouse w
) t
where running_weight > 272
order by added_date desc, ware_id;

内部查询计算 weight 的运行总和，从最近的记录开始，按 added_date 降序(我们使用 ware_id 打破联系)。外层查询取出运行总和超过272的所有记录。

Demo on DB Fiddle

您的初始数据集的总权重为 272。

让我们添加一条权重为 100 的最近记录。

insert into Warehouse values (99, 100, '2019-11-23');

现在查询返回:

ware_id | weight | added_date | running_weight------: | -----: | :--------- | -------------:     19 |     27 | 2012-01-25 |            284     80 |     14 | 2012-01-25 |            298     22 |      2 | 2010-11-22 |            300     10 |     11 | 2010-11-14 |            311     42 |     40 | 2009-10-13 |            351     56 |     21 | 2008-04-04 |            372