MySQL高阶1831-每天的最大交易

题目

编写一个解决方案，报告每天交易金额 amount 最大 的交易 ID 。如果一天中有多个这样的交易，返回这些交易的 ID 。

返回结果根据 transaction_id 升序排列。

准备数据

Create table If Not Exists Transactions (transaction_id int, day date, amount int);Truncate table Transactions;insert into Transactions (transaction_id, day, amount) values ('8', '2021-4-3 15:57:28', '57');insert into Transactions (transaction_id, day, amount) values ('9', '2021-4-28 08:47:25', '21');insert into Transactions (transaction_id, day, amount) values ('1', '2021-4-29 13:28:30', '58');insert into Transactions (transaction_id, day, amount) values ('5', '2021-4-28 16:39:59', '40');insert into Transactions (transaction_id, day, amount) values ('6', '2021-4-29 23:39:28', '58');

分析数据

 第一步:使用开窗函数根据amount降序

select *,rank() over(partition by day order by amount desc) rnfrom transactions;

 第二步:选出最大交易金额的交易ID

with t1 as (select *,rank() over(partition by day order by amount desc) rnfrom transactions
)select transaction_id from t1
where rn =1
order by transaction_id asc;