mysql - 求和子集查询

Question

CREATE TABLE Test (id integer, name varchar(100), weight integer, turn integer);
INSERT INTO Test (id, name, weight, turn) VALUES (5, "George Washington", 250, 1);
INSERT INTO Test (id, name, weight, turn) VALUES (4, "Thomas Jefferson", 175, 5);
INSERT INTO Test (id, name, weight, turn) VALUES (3, "John Adams", 350, 2);
INSERT INTO Test (id, name, weight, turn) VALUES (6, "Thomas Jefferson", 400, 3);
INSERT INTO Test (id, name, weight, turn) VALUES (1, "James Elephant", 500, 6);
INSERT INTO Test (id, name, weight, turn) VALUES (2, "Will Johnliams", 200, 4);

查询应该返回一个只包含一条记录“Thomas Jefferson”的表，因为前三个人将适合(按顺序:George Washingthon、John Adams、Thomas Jefferson)。它们的重量之和为250+350+400

问题是我必须查询最多可容纳 <= 1000 人的电梯中人的位置。我该如何解决这个问题？

Answer 1

我们可以在此处使用相关子查询来检查由轮次顺序给出的人的运行总重量:

SELECT t1.*
FROM Test t1
WHERE (SELECT SUM(t2.weight) FROM Test t2 WHERE t2.turn <= t1.turn) <= 1000
ORDER BY t1.turn DESC
LIMIT 1;

注意:如果您希望所有匹配的人都在体重限制内，则只需从上述查询中删除 LIMIT 子句即可。