Create table If Not Exists Transactions (transaction_id int, day datetime, 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')
写一条 SQL 返回每天交易金额 amount 最大的交易 ID 。如果某天有多个这样的交易,返回这些交易的 ID 。
返回结果根据 transaction_id 升序排列。
查询结果样例如下:
。
select transaction_id
from Transactions
where (date_format(day, '%Y-%m-%d'), amount) in (
select date_format(day, '%Y-%m-%d'), max(amount)
from Transactions
group by date_format(day, '%Y-%m-%d')
)
group by transaction_id
order by transaction_id