StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Let's put it this way: If SQL was a religion I'd go to hell for providing this solution. SQL is not meant to solve this kind of problems, so any solution would be horrible. Mine is no exception :)</p> <pre><code>set @limitValue := 50; select id, newQty1, newQty2, newQty3, newQty4 from ( select id, if(@limitValue - qty1 > 0, qty1, greatest(@limitValue, 0)) newQty1, @limitValue := @limitValue - qty1 Total1, if(@limitValue - qty2 > 0, qty2, greatest(@limitValue, 0)) newQty2, @limitValue := @limitValue - qty2 Total2, if(@limitValue - qty3 > 0, qty3, greatest(@limitValue, 0)) newQty3, @limitValue := @limitValue - qty3 Total3, if(@limitValue - qty4 > 0, qty4, greatest(@limitValue, 0)) newQty4, @limitValue := @limitValue - qty4 Total4 from ( select id, qty1, qty2, qty3, qty4, @rowTotal < @limitValue Useful, @previousRowTotal := @rowTotal PreviousRowTotal, @rowTotal := @rowTotal + qty1 + qty2 + qty3 + qty4 AllRowsTotal, @rowTotal - @previousRowTotal CurrentRowTotal from t, (select @rowTotal := 0, @previousRowTotal := 0) S1 ) MarkedUseful where useful = 1 ) Final </code></pre> <p>For the provided data, this results in:</p> <pre><code>+----+---------+---------+---------+---------+ | ID | NEWQTY1 | NEWQTY2 | NEWQTY3 | NEWQTY4 | +----+---------+---------+---------+---------+ | 1 | 0 | 0 | 10 | 20 | | 2 | 1.5 | 0 | 7.5 | 11 | +----+---------+---------+---------+---------+ </code></pre> <p>And the complement:</p> <pre><code>set @limitValue := 50; select t1.id, coalesce(t1.qty1 - newQty1, t1.qty1) newQty1, coalesce(t1.qty2 - newQty2, t1.qty2) newQty2, coalesce(t1.qty3 - newQty3, t1.qty3) newQty3, coalesce(t1.qty4 - newQty4, t1.qty4) newQty4 from t t1 left join ( select id, if(@limitValue - qty1 > 0, qty1, greatest(@limitValue, 0)) newQty1, @limitValue := @limitValue - qty1 Total1, if(@limitValue - qty2 > 0, qty2, greatest(@limitValue, 0)) newQty2, @limitValue := @limitValue - qty2 Total2, if(@limitValue - qty3 > 0, qty3, greatest(@limitValue, 0)) newQty3, @limitValue := @limitValue - qty3 Total3, if(@limitValue - qty4 > 0, qty4, greatest(@limitValue, 0)) newQty4, @limitValue := @limitValue - qty4 Total4 from ( select id, qty1, qty2, qty3, qty4, @rowTotal < @limitValue Useful, @previousRowTotal := @rowTotal PreviousRowTotal, @rowTotal := @rowTotal + qty1 + qty2 + qty3 + qty4 AllRowsTotal, @rowTotal - @previousRowTotal CurrentRowTotal from t, (select @rowTotal := 0, @previousRowTotal := 0) S1 ) MarkedUseful where useful = 1 ) Final on t1.id = final.id where Total1 < 0 or Total2 < 0 or Total3 < 0 or Total4 < 0 or final.id is null </code></pre> <p>For the provided data, this results in:</p> <pre><code>+----+---------+---------+---------+---------+ | ID | NEWQTY1 | NEWQTY2 | NEWQTY3 | NEWQTY4 | +----+---------+---------+---------+---------+ | 2 | 0 | 0 | 0 | 7 | | 3 | 1 | 2 | 7.5 | 18 | | 4 | 0 | 0.5 | 5 | 13 | +----+---------+---------+---------+---------+ </code></pre> <p>Enjoy!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload