Leetcode1204题解：最后一个能进电梯的人（中等难度）

题目
表: Queue

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| person_id   | int     |
| person_name | varchar |
| weight      | int     |
| turn        | int     |
+-------------+---------+

person_id 是这个表的主键。
该表展示了所有等待电梯的人的信息。
表中 person_id 和 turn 列将包含从 1 到 n 的所有数字，其中 n 是表中的行数。

电梯最大载重量为 1000。

写一条 SQL 查询语句查找最后一个能进入电梯且不超过重量限制的 person_name 。题目确保队列中第一位的人可以进入电梯。

查询结果如下所示 :

Queue 表

+-----------+-------------------+--------+------+
| person_id | person_name       | weight | turn |
+-----------+-------------------+--------+------+
| 5         | George Washington | 250    | 1    |
| 3         | John Adams        | 350    | 2    |
| 6         | Thomas Jefferson  | 400    | 3    |
| 2         | Will Johnliams    | 200    | 4    |
| 4         | Thomas Jefferson  | 175    | 5    |
| 1         | James Elephant    | 500    | 6    |
+-----------+-------------------+--------+------+

Result 表

+-------------------+
| person_name       |
+-------------------+
| Thomas Jefferson  |
+-------------------+

为了简化，Queue 表按 trun 列由小到大排序。
上例中 George Washington(id 5), John Adams(id 3) 和 Thomas Jefferson(id 6) 将可以进入电梯,因为他们的体重和为 250 + 350 + 400 = 1000。
Thomas Jefferson(id 6) 是最后一个体重合适并进入电梯的人。

产生数据

DROP TABLE Queue;

CREATE TABLE Queue(person_id INT,
person_name VARCHAR(20),
weight INT,
turn INT);

INSERT INTO Queue VALUE(5, 'George Washington', 250, 1),
(3, 'John Adams', 350, 2),
(6, 'Thomas Jefferson', 400, 3),
(2, 'Will Johnliams', 200, 4),
(4, 'Thomas Jefferson', 175, 5),
(1, 'James Elephant', 500, 6);

解答
按turn计算累计体重即可把累计小于1000的选出排序选出最大累计对应的人名即可

SELECT Q.`person_name`, @cum_weight:=@cum_weight + Q.weight AS cum_weight
FROM Queue AS Q, (SELECT @cum_weight:=0) AS init
ORDER BY Q.`turn` ASC;

SELECT tmp.`person_name` AS `person_name`
FROM (SELECT Q.`person_name`, @cum_weight:=@cum_weight + Q.weight AS cum_weight
FROM Queue AS Q, (SELECT @cum_weight:=0) AS init
ORDER BY Q.`turn` ASC) AS tmp
WHERE tmp.cum_weight <=1000
ORDER BY tmp.cum_weight DESC
LIMIT 0,1;  #偏移0  选取1

别的解答
老实讲很不擅长写这种代码
先选出累计体重小于1000的用户

SELECT Q.person_name
FROM Queue AS Q
WHERE (SELECT SUM(weight) FROM Queue AS Q1 WHERE Q1.turn <= Q.turn) <= 1000

然后按照turn降序选出第一个即可

SELECT Q.person_name
FROM Queue AS Q
WHERE (SELECT SUM(weight) FROM Queue AS Q1 WHERE Q1.turn <= Q.turn) <= 1000
ORDER BY Q.`turn` DESC
LIMIT 0,1;

与我的方法类似用自连接即可

select a.person_name
from queue a, queue b
where a.turn >= b.turn
group by a.person_id
having sum(b.weight) <= 1000
order by a.turn desc
limit 1