问题描述

我在里面有数据库 3 个表:A、B、C

I have Database inside it 3 tables: A, B, C

A : (项目、数量、位置);B:(项目，数量，地点)；C:(Item,Loc1,Loc2,Loc3,Loc4......,Loc16);

A : (Item , Qty, Loc); B: (Item , Qty,Loc); C:(Item,Loc1,Loc2,Loc3,Loc4.......,Loc16) ;

我需要一个函数来自动计算并通过以下方式自动更新 C 上的值:

I need a function for automate calculation and update the values on C automatically in the following way:

Sum(A.qty)-Sum(B.Qty)，其中 A.Item=B.Item 且 A.Loc = B.Loc；结果应该在 C 中的正确列中更新:从 Loc1 到 Loc16

Sum(A.qty)-Sum(B.Qty) where A.Item=B.Item and A.Loc = B.Loc; Results should be update in C in the correct Column : From Loc1 to Loc16

在A或B中添加任何值时，应在C中进行自动计算.在C中，项目是唯一的.在 A 和 B 中，可以在不同位置的许多交易中找到项目.

When any value is added in A or B, auto calculation should be performed in C. In C, items are unique. In A and B items can be found in many transactions in different locations.

推荐答案

我建议您创建一个提供所需信息的视图，而不是创建更难以跟进的触发器.请注意，我放置了部分查询，需要为 3 到 15 的字段编写相同部分的代码:

I would propose you, better than creating Triggers that are much more difficult to follow-up, create a View that will provide the requested information. Be aware that I put part of the query, need to be write the same portion of code for fields from 3 to 15:

CREATE VIEW C_View
    AS 
SELECT  C.Item, 
        C.Loc1, 
        COALESCE((SELECT SUM(Qty) FROM A WHERE A.Item = C.Item AND A.Loc = C.Loc1), 0) -
        COALESCE((SELECT SUM(Qty) FROM B WHERE B.Item = C.Item AND B.Loc = C.Loc1), 0) 
        AS Qty1,
        C.Loc2, 
        COALESCE((SELECT SUM(Qty) FROM A WHERE A.Item = C.Item AND A.Loc = C.Loc2), 0) -
        COALESCE((SELECT SUM(Qty) FROM B WHERE B.Item = C.Item AND B.Loc = C.Loc2), 0) 
        AS Qty2,
        ...
        C.Loc16, 
        COALESCE((SELECT SUM(Qty) FROM A WHERE A.Item = C.Item AND A.Loc = C.Loc16), 0) -
        COALESCE((SELECT SUM(Qty) FROM B WHERE B.Item = C.Item AND B.Loc = C.Loc16), 0) 
        AS Qty16,
FROM C;

这篇关于数学函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！