本文共 2837 字，大约阅读时间需要 9 分钟。
要求：写一条 SQL 语句，查询：user_id 用户 ID，user_name 用户名，credit 完成交易后的余额，
credit_limit_breached 检查是否透支 （“Yes” 或 “No”），以任意顺序返回结果表。力扣银行 (LCB) 帮助程序员们完成虚拟支付。我们的银行在表 Transaction 中记录每条交易信息，我们要查询每个用户的当前余额，并检查他们是否已透支（当前额度小于 0）。
用户表： 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 的用户转账。
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 |+------------+------------+------------+----------+---------------+
Result Table：
+------------+------------+------------+-----------------------+| 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) = -$100Jonathan 在 "2020-08-02" 收到了 $500 并在 "2020-08-08" 支付了 $200 ，当前额度 (200 +500 -200) = $500Winston 在 "2020-08-01" 收到了 $400 并在 "2020-08-03" 支付了 $500 ，当前额度 (10000 +400 -500) = $9900Luis 未收到任何转账信息，额度 = $800
SQL语句：
with a as(select user_id as id,credit as credit from users union allselect paid_by as id,-amount as credit from transactionsunion allselect paid_to as id,amount as credit from transactions),b as(select id,sum(credit) as creditfrom agroup by id)select b.id as user_id,c.user_name as user_name,b.credit as credit,if(b.credit<0,'Yes','No') as credit_limit_breachedfrom b join users con b.id=c.user_id;
转载地址：http://sngwi.baihongyu.com/