SQL架构
用户表: Users
+--------------+---------+ | Column Name | Type | +--------------+---------+ | user_id | int | | user_name | varchar | | credit | int | +--------------+---------+ user_id 是这个表的主键。 表中的每一列包含每一个用户当前的额度信息。
交易表:Transactions
+---------------+---------+ | Column Name | Type | +---------------+---------+ | trans_id | int | | paid_by | int | | paid_to | int | | amount | int | | transacted_on | date | +---------------+---------+ trans_id 是这个表的主键。 表中的每一列包含银行的交易信息。 ID 为 paid_by 的用户给 ID 为 paid_to 的用户转账。
力扣银行 (LCB) 帮助程序员们完成虚拟支付。我们的银行在表 Transaction 中记录每条交易信息,我们要查询每个用户的当前余额,并检查他们是否已透支(当前额度小于 0)。
写一条 SQL 语句,查询:
user_id
用户 IDuser_name
用户名credit
完成交易后的余额credit_limit_breached
检查是否透支 ("Yes" 或 "No")
以任意顺序返回结果表。
查询格式见如下所示。
示例 1:
输入: Users
表: +------------+--------------+-------------+ | user_id | user_name | credit | +------------+--------------+-------------+ | 1 | Moustafa | 100 | | 2 | Jonathan | 200 | | 3 | Winston | 10000 | | 4 | Luis | 800 | +------------+--------------+-------------+Transactions
表: +------------+------------+------------+----------+---------------+ | trans_id | paid_by | paid_to | amount | transacted_on | +------------+------------+------------+----------+---------------+ | 1 | 1 | 3 | 400 | 2020-08-01 | | 2 | 3 | 2 | 500 | 2020-08-02 | | 3 | 2 | 1 | 200 | 2020-08-03 | +------------+------------+------------+----------+---------------+ 输出: +------------+------------+------------+-----------------------+ |user_id
|user_name
|credit
|credit_limit_breached
| +------------+------------+------------+-----------------------+ | 1 | Moustafa | -100 | Yes | | 2 | Jonathan | 500 | No | | 3 | Winston | 9900 | No | | 4 | Luis | 800 | No | +------------+------------+------------+-----------------------+ Moustafa 在 "2020-08-01" 支付了 $400 并在 "2020-08-03" 收到了 $200 ,当前额度 (100 -400 +200) = -$100 Jonathan 在 "2020-08-02" 收到了 $500 并在 "2020-08-08" 支付了 $200 ,当前额度 (200 +500 -200) = $500 Winston 在 "2020-08-01" 收到了 $400 并在 "2020-08-03" 支付了 $500 ,当前额度 (10000 +400 -500) = $9900 Luis 未收到任何转账信息,额度 = $800
union all:
with t as
(
select
id ,sum(amount) amount # 每个 id 收支求和
from
(
select
paid_by id, -amount amount # 出账
from
Transactions
union all
select
paid_to id, amount # 入账
from
Transactions
) s1
group by id
)
select
u.user_id,u.user_name,u.credit + ifnull(t.amount,0) credit, if(u.credit + ifnull(t.amount,0)<0,'Yes','No')credit_limit_breached
from Users u left join t
on u.user_id = t.id
if嵌套 :
select user_id,user_name,
ifnull((sum((if(paid_by=user_id,-1,1)*amount))+credit),credit) as credit,
if((sum((if(paid_by=user_id,-1,1)*amount))+credit) < 0,'Yes','No') as credit_limit_breached
from users u left join(
select paid_by,paid_to,amount
from transactions
) t
on user_id=paid_by or user_id=paid_to
group by user_id
/*
第一步:确定表连接我们需要的列有'paid_by' or 'paid_to',则需要如下表连接
from users u left join(
select paid_by,paid_to,amount
from transactions) t
on user_id=paid_by or user_id=paid_to
第二步:确定分组,依据为'user_id'
group by user_id
第三步:筛选出我们需要的列,
ifnull((sum((if(paid_by=user_id,-1,1)*amount))+credit),credit) as credit,
if((sum((if(paid_by=user_id,-1,1)*amount))+credit) < 0,'Yes','No') as credit_limit_breached
####因为 'or' 连接是一个双向选择,当不等于时 user_id = paid_by ,则 user_id=paid_to
第四步:合并
作者:hejy-w
链接:https://leetcode.cn/problems/bank-account-summary/solution/chuan-tong-jie-fa-by-hejy-w-pnga/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
*/
笔记:
时刻要注意 为null的值进行运算 要转型