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

143

劳纳克·古普塔（Raunak Gupta）

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

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

id | 支出总计| last_spend 
--------------------------------- 
1 | 30 | 18 
2 | 280 | 120 
3 | 20 | 20

选择a.id，SUM（ash.amount_spend）AS消费_总计
FROM帐户作为内部
联接account_spend_history AS灰启动ash.account_id = a.id 
GROUP BY a.id

我正在获取account.id和ash.amount的总和，但我也需要最后一次支出。如何获得？

谢谢。

下蹲

这是一个使用的选项correlated subquery：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。