1587. 银行账户概要 II

客官°小女子只卖身不卖艺 2023-09-30 11:46 21阅读 0赞

写一个 SQL, 报告余额高于 10000 的所有用户的名字和余额. 账户的余额等于包含该账户的所有交易的总和.

返回结果表单没有顺序要求.

查询结果格式如下例所示.
    
     
    
    Users table:
    +------------+--------------+
    | account    | name         |
    +------------+--------------+
    | 900001     | Alice        |
    | 900002     | Bob          |
    | 900003     | Charlie      |
    +------------+--------------+
    
    Transactions table:
    +------------+------------+------------+---------------+
    | trans_id   | account    | amount     | transacted_on |
    +------------+------------+------------+---------------+
    | 1          | 900001     | 7000       |  2020-08-01   |
    | 2          | 900001     | 7000       |  2020-09-01   |
    | 3          | 900001     | -3000      |  2020-09-02   |
    | 4          | 900002     | 1000       |  2020-09-12   |
    | 5          | 900003     | 6000       |  2020-08-07   |
    | 6          | 900003     | 6000       |  2020-09-07   |
    | 7          | 900003     | -4000      |  2020-09-11   |
    +------------+------------+------------+---------------+
    
    Result table:
    +------------+------------+
    | name       | balance    |
    +------------+------------+
    | Alice      | 11000      |
    +------------+------------+
    Alice 的余额为(7000 + 7000 - 3000) = 11000.
    Bob 的余额为1000.
    Charlie 的余额为(6000 + 6000 - 4000) = 8000.

# Write your MySQL query statement below
    
    select name,sum(amount) as balance
    
    from Users u 
    
    left join Transactions t
    
    on u.account = t.account    
    
    group by name
    
    having balance>10000