最后一个能进入巴士的人(计算前一行某列值与当前行某列值之和)

SQL专题

本文最后更新于2023年09月17日，已超过367天没有更新。如果文章内容或图片资源失效，请留言反馈，我会及时处理，谢谢！

题目：

表: Queue

Column Name	Type
person_id	int
person_name	varchar
weight	int
turn	int

person_id 是这个表具有唯一值的列。
该表展示了所有候车乘客的信息。
表中 person_id 和 turn 列将包含从 1 到 n 的所有数字，其中 n 是表中的行数。
turn 决定了候车乘客上巴士的顺序，其中 turn=1 表示第一个上巴士，turn=n 表示最后一个上巴士。
weight 表示候车乘客的体重，以千克为单位。

有一队乘客在等着上巴士。然而，巴士有1000 千克的重量限制，所以其中一部分乘客可能无法上巴士。

编写解决方案找出最后一个上巴士且不超过重量限制的乘客，并报告 person_name 。题目测试用例确保顺位第一的人可以上巴士且不会超重。

返回结果格式如下所示。

示例 1：

输入：

Queue 表
person_id	person_name	weight	turn
5	Alice	250	1
4	Bob	175	5
3	Alex	350	2
6	John Cena	400	3
1	Winston	500	6
2	Marie	200	4

输出：
person_name
John Cena

解释：

为了简化，Queue 表按 turn 列由小到大排序。
Turn	ID	Name	Weight	Total Weight
1	5	Alice	250	250
2	3	Alex	350	600
3	6	John Cena	400	1000	(最后一个上巴士)
4	2	Marie	200	1200	(无法上巴士)
5	4	Bob	175	_
6	1	Winston	500	_

这道题的难点在于如何计算当前的weight值与上一行的 Total Weight 之和，判断是否大于等于1000；
一开始尝试使用LAG函数，但是LAG函数无法统计表中原本未有的列

方法1：使用变量

SELECT
    a.person_name
FROM
(SELECT
    turn
    ,person_name
    ,weight
    ,@prev := @prev + weight AS Total
FROM
    Queue,(SELECT @prev := 0) AS Vars
ORDER BY turn
) a
WHERE 
    a.Total <= 1000
ORDER BY a.turn DESC
LIMIT 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

我们只需要计算出每个人进去后的总和，找到总和小于等于 1000 的最后一个人即可。于是我们可以使用自连接的方法计算直到当前人的重量的总和
我们使用 a 表的 person_id 表示自身，b 表中的数据表示为包括自己在内的所有人。使用查询条件 a.turn >= b.turn 找到所有在他之前以及他自己的重量。再使用 SUM 计算总和并过滤掉大于 1000 的数据

使用自连接，可以限制函数SUM统计到哪一行

朗读

Toasobi的博客

最后一个能进入巴士的人(计算前一行某列值与当前行某列值之和)

互动读者

热门文章