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) rn
       from transactions;

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

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

 

原文地址：https://blog.csdn.net/weixin_58305115/article/details/142346571

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！