当前位置：首页 > news >正文

MySQL高阶2066-账户余额

news 2025/6/28 23:47:27

题目

准备数据

分析数据

总结

题目

请写出能够返回用户每次交易完成后的账户余额. 我们约定所有用户在进行交易前的账户余额都为0，并且保证所有交易行为后的余额不为负数。

返回的结果请依次按照账户（account_id), 日期( day ) 进行升序排序 .

准备数据

Create table If Not Exists Transactions (account_id int, day date, type ENUM('Deposit', 'Withdraw'), amount int)Truncate table Transactionsinsert into Transactions (account_id, day, type, amount) values ('1', '2021-11-07', 'Deposit', '2000')insert into Transactions (account_id, day, type, amount) values ('1', '2021-11-09', 'Withdraw', '1000')insert into Transactions (account_id, day, type, amount) values ('1', '2021-11-11', 'Deposit', '3000')insert into Transactions (account_id, day, type, amount) values ('2', '2021-12-07', 'Deposit', '7000')insert into Transactions (account_id, day, type, amount) values ('2', '2021-12-12', 'Withdraw', '7000')

分析数据

selectaccount_id, day,sum(case when type = 'Deposit' then amount else -amount end) over (partition by account_id order by day) balance
fromtransactions
order byaccount_id,day;