php - 如何在mysql中插入一个表的列的SUM作为另一个表的列的值？

Question

我有两个名为'register' 和'customer' 的表。注册表如下所示:

id     customer_id     purchase     paid     discount     return     previous_due
1      97              500          200      50                      100
2      98              1500         700      150                     500
3      97                                                 70
4      99              900          900      0                       1000
5      98                           200
6      99              1200         1000

我希望通过 customer_id 计算每列的 SUM 并自动更新“customer”表的列。 “客户”表如下所示:

customer_id  tot_pur  tot_paid  tot_disc  tot_return  tot_due  final_due
97           500      200       50        0           100      350
98
99

final_due 列的计算方式类似于 (tot_pur + tot_due) - (tot_paid + tot_disc + tot_return)

我不擅长 mysql，所以最好和最简单的方法可以拯救我。任何帮助表示赞赏。提前致谢。

Answer 1

老实说，除非您需要以闪电般的速度使用这些总和，否则我可能建议只单独存储每笔交易的值(value)，然后创建一个按客户聚合并找到总和的 View :

CREATE VIEW register_sums AS (
    SELECT
        customer_id,
        SUM(purchase) AS tot_pur,
        SUM(paid) AS tot_paid,
        SUM(discount) AS tot_disc, 
        SUM(return) AS tot_return,
        SUM(previous_due) AS tot_due,
        SUM(purchase) + SUM(paid) - SUM(discount) - SUM(return) -
            SUM(previous_due) AS final_due
    FROM register
    GROUP BY customer_id
)