单挑力扣（LeetCode）SQL笔试题：2388. 将表中的空值更改为前一个值（难度：中等）

表: CoffeeShop+-------------+---------+| Column Name | Type    |+-------------+---------+| id          | int     || drink       | varchar |+-------------+---------+id 是该表的主键。该表中的每一行都显示了订单 id 和所点饮料的名称。一些饮料行为 null。
编写一个 SQL 查询，将 drink 的 null值替换为前面最近一行不为 null的 drink。保证表第一行的 drink 不为 null。返回与输入顺序相同的结果表。
查询结果格式示例如下。示例 1:输入:CoffeeShop 表:+----+------------------+| id | drink            |+----+------------------+| 9  | Mezcal Margarita || 6  | null             || 7  | null             || 3  | Americano        || 1  | Daiquiri         || 2  | null             |+----+------------------+输出: +----+------------------+| id | drink            |+----+------------------+| 9  | Mezcal Margarita || 6  | Mezcal Margarita || 7  | Mezcal Margarita || 3  | Americano        || 1  | Daiquiri         || 2  | Daiquiri         |+----+------------------+解释: 对于 ID 6，之前不为空的值来自 ID 9。我们将 null 替换为 “Mezcal Margarita”。对于 ID 7，之前不为空的值来自 ID 9。我们将 null 替换为 “Mezcal Margarita”。对于 ID 2，之前不为空的值来自 ID 1。我们将 null 替换为 “Daiquiri”。请注意，输出中的行与输入中的行相同。
来源：力扣（LeetCode）链接：https://leetcode.cn/problems/change-null-values-in-a-table-to-the-previous-value

#测试数据Create table If Not Exists CoffeeShop (id int, drink varchar(20));
insert into CoffeeShop (id, drink) values ('9', 'Mezcal Margarita');insert into CoffeeShop (id, drink) values ('6', 'None');insert into CoffeeShop (id, drink) values ('7', 'None');insert into CoffeeShop (id, drink) values ('3', 'Americano');insert into CoffeeShop (id, drink) values ('1', 'Daiquiri');insert into CoffeeShop (id, drink) values ('2', 'None');

select     id,    drink,    row_number() over() rnfrom CoffeeShop;

withtmp as (    select         id,        drink,        row_number() over() rn    from CoffeeShop)select    c.id,    c.drinkfrom (    select        a.id,        coalesce(a.drink,b.drink) drink,        a.rn,        row_number() over(partition by a.id order by b.rn desc) rn2    from tmp a    left join tmp b    on a.rn > b.rn    and b.drink is not null)cwhere c.rn2 = 1order by c.rn;