如何将项目的发票总额更新到IS或Business两列之一

本文关键字：Business 两列 IS 项目 更新 | 更新日期: 2023-09-27 18:17:55

我有两个表:

一个是发票表，它有一个名为BusinessOrIS的查找表，用于识别它是"is"还是"Business";还有一个已花费的栏，用于显示发票的金额。

另一个表是项目表，它有两列需要更新'totalISSpent'和'totalBusinessSpent'。

所以我要做的是取花费金额的总和，其中BusinessOrIS =1(is)和发票。投影=项目。以及BusinessOrIS =2(商业)和发票。投影=项目。并将其滚动到'totalISSpent'和'totalBusinessSpent'

//我现在所拥有的是在InsertButton_Click事件的c#一侧:

//输入co 

SqlConnection db = new SqlConnection("mystring")SqlTransaction transaction;
db.Open()
transaction = db.BeginTransaction();
UPDATE Projects Set ProjectSpentIS = (SELECT SUM(Invoices.Spent) 
FROM Invoices WHERE Invoices.CostToISorBusiness ='1' 
AND Projects.Projectid = Invoices.ProjectID)
UPDATE Projects Set ProjectSpentBusiness = 
(SELECT SUM(Invoices.Spent) FROM Invoices
    WHERE Invoices.CostToISorBusiness ='2' AND 
    Projects.Projectid = Invoices.ProjectID)
transaction.Commit();

如何将项目的发票总额更新到IS或Business两列之一

我想我可能对你的模式设计有问题(因为你似乎有一个多域表，这是一个禁忌，或者至少应该为将来的扩展设置数据库)。但是，你还没有发布足够的信息来建议修改。

但是，您应该能够将UPDATE重述到一个SQL语句中:

UPDATE Projects as a 
SET ProjectSpentIS = COALESCE((SELECT SUM(b.Spent)
                               FROM Invoices as b
                               WHERE b.CostToISOrBusiness = '1'
                               AND b.ProjectId = a.ProjectId), ProjectSpentIS),
    ProjectSpentBusiness = COALESCE((SELECT SUM(b.Spent)
                                     FROM Invoices as b
                                     WHERE b.CostToISOrBusiness = '2'
                                     AND b.ProjectId = a.ProjectId), 
                                   ProjectSpentBusiness)
WHERE EXISTS (SELECT '1'
              FROM Invoices as b
              WHERE b.ProjectId = a.ProjectId
              AND b.CostToISOrBusiness in ('1', '2'))
相关文章：