MYSQL查询获取Group BY中的SUM和LAST记录值

Question

我有两个表 1 是 account，另一个是 account_spend_history AS ash account 是父表/主表，而 shin 是子表。并且与帐户具有 OneToMany 关系(帐户表 ID 是 ash 中的外键，如 account_id)。请看图片

现在我需要获取 account.id、总支出(这是具有相同 account_id 的 amount_spend 的总和)和上次支出(是插入 ash 表中的最后一条记录和 account_id，即与 MAX(ash) 对应的 amount_spend 值.id)), 即

id  |  spend_total  |  last_spend---------------------------------1   |  30           |   182   |  280          |   1203   |  20           |   20

SELECT a.id, SUM(ash.amount_spend) AS spend_totalFROM accounts as aINNER JOIN account_spend_history AS ash ON ash.account_id = a.idGROUP BY a.id

我得到了 account.id 和 ash.amount 花费的总和，但我还需要上次花费。如何获得？

谢谢。

Answer 1

这是一个使用相关子查询的选项:

SELECT a.id, SUM(ash.amount_spend) AS spend_total,
       (SELECT amount_spend 
        FROM account_spend_history as ash2 
        WHERE ash2.account_id = a.id 
        ORDER BY ash2.id DESC LIMIT 1) as last_spend
FROM accounts as a
    INNER JOIN account_spend_history AS ash ON ash.account_id = a.id
GROUP BY a.id