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?



  • 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 desc By 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 < 7 The 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

    Исходные данные для запроса

    It does.

    выдача


    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 < 7 and 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 nvl Total /questions/tagged/mysql Yes IFNULL

    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.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2