Upstream to a schedule and a total amount, schedule of field is (product Numbers, the company number, amount), for the detail amount shall be carried out in accordance with the product and company group sum, which is the same product number under each company, the total amount of the upstream to the amount of detail data fields for accuracy of 8-bit decimal places, just add up all the amount is an integer (total amount), but our program will be to query the data directly into the other table b, the amount of this table b field for two precision, so after rounding is inserted into a table b will produce the tail is poor, also is the total amount is not equal to the total amount in table b, excuse me, how to solve, my idea is to check the results are rounded and then with the same total amount of the product Numbers in the schedule of the secondary judgment, if the same product number amount under the agreement, do not do, if a difference of 1 or 2 cents, directly on the query result plus or minus the tail is poor, but now I don't know how to check out results for secondary processing, is a big bother to guide everyone

CodePudding user response:

Table structure, test data, the expected results,

CodePudding user response:

- A list - products company dimensions B summary - the product dimension
Merge into A
USING (select rowid AS RD,
Amy polumbo roduct,
A.com pany,
A. alance,
Row_number () over (partition by Amy polumbo roduct order by a. alance desc) as an rn, - according to the amount of sorting, grouping product dimensions, take the largest amount of the corresponding column
(sum (a. alance) over (partition by Amy polumbo roduct) - b.t ot_balance) as minus_balance - amount balance
From A, B
Where Amy polumbo roduct=p. roduct) T
ON (Amy polumbo roduct=t.p roduct and t.r n=1 and a.r owid=pany t.r d and t.com=a.com pany)
The when matched then
Update the set a. alance=a. alance - t.m inus_balance - out