SQL如何从另一个表求和并插入另一个表 [英] SQL How to sum from another table and insert in another table

问题描述

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

For now I sum in Delphi app and then insert it in SQL database
but I want database to sum and insert automatically when I insert new ExpenseAmount
I have 2 tables

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

我想为ProjectsTable中的每个单个项目自动汇总ExpenseAmount并将其插入ProjectsTable
中的ExpenseTotal 我想在Delphi

I want automatically sum ExpenseAmount for every single project in ProjectsTable and insert it in ExpenseTotal from ProjectsTable
I want to do that in SQL all out of Delphi

推荐答案

将此触发器添加到您的ExpenseTable

Add this trigger to your 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

Don't forget to add trigger After Update and After Delete to update ExpenseTotal

这篇关于SQL如何从另一个表求和并插入另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！