单挑力扣（LeetCode）SQL笔试题：2314. 每个城市最高气温的第一天（难度：中等）

表: Weather+-------------+------+| Column Name | Type |+-------------+------+| city_id     | int  || day         | date || degree      | int  |+-------------+------+(city_id, day) 是该表的主键。该表中的每一行都包含某一天某个城市的天气程度。所有的温度都是在 2022 年。
编写一个 SQL 来查询每个城市中有最高温度记录的日子。如果同一城市多次记录最高气温，则返回其中最早的一天。返回按 city_id 升序排序的结果表。
查询结果格式示例如下。示例 1:输入: Weather 表:+---------+------------+--------+| city_id | day        | degree |+---------+------------+--------+| 1       | 2022-01-07 | -12    || 1       | 2022-03-07 | 5      || 1       | 2022-07-07 | 24     || 2       | 2022-08-07 | 37     || 2       | 2022-08-17 | 37     || 3       | 2022-02-07 | -7     || 3       | 2022-12-07 | -6     |+---------+------------+--------+输出: +---------+------------+--------+| city_id | day        | degree |+---------+------------+--------+| 1       | 2022-07-07 | 24     || 2       | 2022-08-07 | 37     || 3       | 2022-12-07 | -6     |+---------+------------+--------+解释: 城市 1 的最高气温出现在 2022-07-07，为24度。城市 2 的最高气温出现在 2022-08-07 和 2022-08-17，为37度。我们选择较早的日期 (2022-08-07)。城市 3 的最高气温记录在 2022-12-07 年，为-6 度。
来源：力扣（LeetCode）链接：https://leetcode.cn/problems/the-first-day-of-the-maximum-recorded-degree-in-each-city

#测试数据Create table If Not Exists Weather (city_id int, day date, degree int);
insert into Weather (city_id, day, degree) values ('1', '2022-01-07', '-12');insert into Weather (city_id, day, degree) values ('1', '2022-03-07', '5');insert into Weather (city_id, day, degree) values ('1', '2022-07-07', '24');insert into Weather (city_id, day, degree) values ('2', '2022-08-07', '37');insert into Weather (city_id, day, degree) values ('2', '2022-08-17', '37');insert into Weather (city_id, day, degree) values ('3', '2022-02-07', '-7');insert into Weather (city_id, day, degree) values ('3', '2022-12-07', '-6');

select    a.city_id,    a.day,    a.degreefrom (    select        city_id,        day,        degree,        row_number() over(partition by city_id order by degree desc,day) rn    from Weather)awhere a.rn = 1order by a.city_id;