mysql - SQL如何从另一个表求和并插入另一个表

现在，我在Delphi应用程序中求和，然后将其插入SQL数据库中
但是我希望数据库在插入新的ExpenseAmount时自动求和并插入
我有2张桌子

ProjectsTable (ProjectID, ProjectName, Cost, ExpenseTotal, CostRemaining)
ExpenseTable (ID, ProjectID, ExpenseAmount, ExpenseDate)

我要为ExpenseAmount中的每个项目自动汇总ProjectsTable并将其从ExpenseTotal插入ProjectsTable
我想在SQL中全部在Delphi中执行此操作

最佳答案

将此触发器添加到您的ExpenseTable

CREATE TRIGGER ExpenseSum AFTER INSERT ON ExpenseTable FOR EACH ROW
BEGIN
    UPDATE ProjectsTable P
    SET ExpenseTotal =
    (SELECT SUM(ExpenseAmount) from ExpenseTable
    where ExpenseTable.ProjectID= P.ProjectID)
    where P.ProjectID = New.ProjectID;
END

不要忘记添加触发器After Update和After Delete来更新ExpenseTotal

关于mysql - SQL如何从另一个表求和并插入另一个表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52090855/