57 最后一个能进入巴士的人

[astak16]

题目

题目链接：最后一个能进入巴士的人

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

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

返回结果格式如下所示。

Create table If Not Exists Queue (person_id int, person_name varchar(30), weight int, turn int);
Truncate table Queue;
insert into Queue (person_id, person_name, weight, turn) values ('5', 'Alice', '250', '1');
insert into Queue (person_id, person_name, weight, turn) values ('4', 'Bob', '175', '5');
insert into Queue (person_id, person_name, weight, turn) values ('3', 'Alex', '350', '2');
insert into Queue (person_id, person_name, weight, turn) values ('6', 'John Cena', '400', '3');
insert into Queue (person_id, person_name, weight, turn) values ('1', 'Winston', '500', '6');
insert into Queue (person_id, person_name, weight, turn) values ('2', 'Marie', '200', '4');

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

输出：
+-------------+
| 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    | ___          |
+------+----+-----------+--------+--------------+

解析

本题考察了一个知识点：如何按行累加

在 mysql 实现这种功能，有三种方式：

1. 自连接，然后按照 a 表分组，聚合 b 表
2. 使用窗口函数
3. 使用变量和子查询

使用窗口函数和变量的形式都涉及到子查询：

• with 子查询：类似与公共表表达式
• from 子查询：类似与临时表

方法一

使用自连接，连接条件是 a.turn >= b.turn，然后按照 a.person_id 和 a.turn 分组

为什么是按照 a 表去分组？

因为按照 a 表分组，聚合的是 b 表

两张表自连接的条件是 a.turn >= b.turn，这样就可以保证 b 表的 turn 是小于等于 a 表的 turn

使用 group by 分组后，b 表的所有字段是聚合的

我们使用 sum 函数，对 b.weight 字段进行求和，就可以得到每行的值

SELECT
  a.person_name
FROM
  Queue a, Queue b
WHERE
  a.turn >= b.turn
GROUP BY
  a.person_name, a.turn
HAVING	SUM( b.weight ) <= 1000 ORDER BY a.turn DESC
LIMIT 1;

方法二

使用窗口函数，可以进行累加

1. 在 sum() 函数后面加上 over 就可以按行累加了
2. 使用 with 子查询，将查出来的数据放在临时表中 tmp 中
3. 对临时表进行查询筛选出当前重量小于 1000 的数据

WITH tmp AS (
  SELECT person_name, sum( weight ) over ( ORDER BY turn ) AS weight_sum FROM	Queue
)
SELECT person_name FROM tmp WHERE weight_sum <= 1000 ORDER BY weight_sum DESC LIMIT 1;

方法三

使用子查询和变量的形式，累加行

1. ( SELECT @pre := 0 ) tmp 初始化变量
2. @pre := @pre + weight AS sum_weight 累加变量
3. 使用 from 子查询，对临时表进行查询，筛选出当前重量小于 1000 的数据

SELECT
  tmp.person_name
FROM (
  SELECT
    person_name, @pre := @pre + weight AS sum_weight
  FROM Queue, ( SELECT @pre := 0 ) tmp ORDER BY turn
) tmp
WHERE tmp.sum_weight <= 1000 ORDER BY tmp.sum_weight DESC LIMIT 1