MySQL高阶2066-账户余额

目次
标题
预备数据
分析数据
总结

---

标题

请写出可以或许返回用户每次买卖业务完成后的账户余额. 我们约定全部用户在举行买卖业务前的账户余额都为0， 而且包管全部买卖业务运动后的余额不为负数。
返回的结果请依次按照 账户（account_id), 日期( day ) 举行升序排序 .
预备数据

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

复制代码

分析数据

1. select
2.     account_id, day,
3.     sum(case when type = 'Deposit' then amount else -amount end) over (partition by account_id order by day) balance
4. from
5.     transactions
6. order by
7.     account_id,day;

复制代码

总结

   利用开窗函数sum()盘算差别的买卖业务范例

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！qidao123.com:ToB企服之家，中国第一个企服评测及软件市场,开放入驻,技术点评得现金