Choose lines where the value of the cells does not exceed a certain number
There's a balance of merchandise #1 like 7th.
There are 16 lines in the table of parishes, with different quantities, and for example 6 lines in the table of expenditure
Ultimately, knowing the final balance, I need to form a request for a table of parishes from the and select only those lines that include this balance.
For example, recent parishes
line 13 - 2nd
line 14 - 4°
line 15 - 2nd
Should choose a line of 15,200 and take the price of parish.
line 14 4° and price
and line 13 1st and price of parish. ♪
How do you put such a request into a database?
Mystic last edited by
Unfortunately, the commentaries indicated that /questions/tagged/mysql does not support the synthaxis in the first part of the answer. I will also note that the task can be accomplished through the usual procedures or by a change in the architectural approach. Well or SBD)
For CBD support standard SQL-2008
To meet the challenge, a request is required using the expression
over(partition by...which will allow the purchase of the sum of the goods. Call attention.
(partition by ...)Added
order by p.prihoddate descBy the time the date of arrival was lost, so the accumulation would go as the parish falls. Next, on the outside request, we're detecting
where nsum - prihod < 7The amounts that were transferred for our balance. Please note that, if there is a subtraction, it is in order to allow a partially spent parish to enter the sample. Next, we need to count the parish residues, and then we have to use the probationary operator.
case when nsum < 7 then prihod else prihod - (nsum - 7) end ostatokI hope you don't have a problem picking up analog. /questions/tagged/mysql ♪ The essence of the condition that for unspent parishes we shall remove the balance of the same parish and, for part- expended, we shall take the difference between the accumulated amount and the total balance of the goods. I'm sorry. /questions/tagged/oracle
select tovarid, case when nsum < 7 then prihod else prihod - (nsum - 7) end ostatok, prihodprice, prihoddate from (select p.*, sum(p.prihod) over (partition by p.tovarid order by p.prihoddate desc) nsum from prihods p where p.tovarid = 1) where nsum - prihod < 7
Reference data for request
Decision for earlier SQL standards
But the money can be taken by another, adding another level of sub-request.
select tovarid, case when psum + prihod < 7 then prihod else (7 - psum ) end ostatok, prihodprice, prihoddate from (select p.*, nvl((select sum(p2.prihod) from prihods p2 where p2.prihoddate > p.prihoddate and p2.tovarid = p.tovarid),0) psum from prihods p where p.tovarid = 1) where psum < 7
Pay attention to the retention monies now, without taking into account the arrival of the current line, so the selection condition changed slightly.
psum < 7and Conditional operator
case when psum + prihod < 7 then prihod else (7 - psum ) end ostatok♪ Now I don't see any obstacles to responding to the request for /questions/tagged/mysql ♪ As long as he's optimistic not to judge. Note that the operator has been added in the calculations
nvl(...,0)Total /questions/tagged/oracle If the first parameter null, he returns the second parameter, in the remaining cases the first. We need it to be psum 0 when the internal request doesn't return any line. Analogom
nvlTotal /questions/tagged/mysql Yes
P. S. It would seem that there was a lack of final functions. /questions/tagged/mysql but the top request could not be easily rewritten to a sample of several items, but the bottom is more complex. Well, the optimism in the top will certainly win.