sql如何从另一个表求和并插入到另一个表中

oalqel3c 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(425)

现在我算上 Delphi 应用程序，然后将其插入 SQL 数据库
但我想数据库的总和和插入时，我插入新的自动 ExpenseAmount 我有两张table

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

我要自动求和 ExpenseAmount 对于每个项目 ProjectsTable 并将其插入 ExpenseTotal 从 ProjectsTable 我想在家里做 SQL 全部用完 Delphi

sql mysql Database

来源：https://stackoverflow.com/questions/52090855/sql-sum-of-payments

2条答案

按热度按时间

fcy6dtqo1#

将此触发器添加到您的费用表中

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

赞(0）回复(0）举报 2021-06-20

pftdvrlh2#

您可以创建一个子查询，将每个项目的费用金额分组，然后更新projects表。

UPDATE ProjectsTable
SET ProjectsTable.ExpenseTotal = S1.ExpenseAmount
FROM ProjectsTable
INNER JOIN (SELECT ProjectID, SUM(ExpenseAmount) as ExpenseAmount
FROM ExpenseTable
GROUP BY ProjectID) as S1
ON ProjectsTable.ProjectID = S1.ProjectID

赞(0）回复(0）举报 2021-06-20

我来回答

sql如何从另一个表求和并插入到另一个表中

2条答案

相关问题

热门标签

最新问答