CODEWITHSUNDEEP
mysqlsql
Vincent Chen
Vincent Chen

Reputation: 27

How to query result list from a table that sum(it's specific column value) equal an input value

I hava a table like:

--------------------------------------
| ID  |  AMOUNT | MIN_AMOUNT | TIME  |
--------------------------------------
|  1  |  100.00 |   100.00   | TIME1 |
|  2  |  200.00 |   100.00   | TIME2 |
|  3  |  200.00 |   100.00   | TIME3 |
--------------------------------------

I need to query from this table that the sum of AMOUNT(if the last amount is between MIN_AMOUNT and AMOUNT, could use MIN_AMOUNT) equals an input value, note that i have already sorted priority by ID, for example:

  1. if input is 500.00, result will be 1,2,3
  2. if input is 400.00, result will also be 1,2,3(3 will give 100.00)
  3. if input is 300.00, result will be 1,2
  4. if input is 200.00, result will be 2(1 is not enough)

Any hints is welcome!

Upvotes: 0

Views: 50

Answers (1)

Strawberry
Strawberry

Reputation: 33935

E.g.:

SELECT a.* 
  FROM my_table a 
  JOIN 
     ( SELECT MIN(id) id 
         FROM 
            ( SELECT x.*
                   , @i:=@i+amount i 
                FROM my_table x
                   , (SELECT @i:=0) vars 
               ORDER  
                  BY x.id
            ) n 
        WHERE i >= 100
     ) b 
    ON b.id >= a.id;

Upvotes: 0

Related Questions