mysql amount_Select rows until a total amount is met in a column (mysql)

问题

I have seen this issue in SF, but me being a noob I just can't get my fried brain around them. So please forgive me if this feels like repetition.

My Sample Table

--------------------------

ID | Supplier | QTY

--------------------------

1 1 2

2 1 2

3 2 5

4 3 2

5 1 3

6 2 4

I need to get the rows "UNTIL" the cumulative total for "QTY" is equal or greater than 5 in descending order for a particular supplier id.

In this example, for supplier 1, it will be rows with the ids of 5 and 2.

Id - unique primary key

Supplier - foreign key, there is another table for supplier info.

Qty - double

回答1:

How about this? Using two variables.

SQLFIDDLE DEMO

Query:

set @tot:=0;

set @sup:=0;

select x.id, x.supplier, x.ctot

from (

select id, supplier, qty,

@tot:= (case when @sup = supplier then

@tot + qty else qty end) as ctot,

@sup:=supplier

from demo

order by supplier asc, id desc) x

where x.ctot >=5

;

| ID | SUPPLIER | CTOT |

------------------------

| 2 | 1 | 5 |

| 1 | 1 | 7 |

| 3 | 2 | 5 |

回答2:

It ain't pretty, but I think this does it and maybe it can be the basis of something less cumbersome. Note that I use a "fake" INNER JOIN just to get some variable initialized for the first time--it serves no other role.

SELECT ID,

supplier,

qty,

cumulative_qty

FROM

(

SELECT

ID,

supplier,

qty,

-- next line keeps a running total quantity by supplier id

@cumulative_quantity := if (@sup <> supplier, qty, @cumulative_quantity + qty) as cumulative_qty,

-- next is 0 for running total < 5 by supplier, 1 the first time >= 5, and ++ after

@reached_five := if (@cumulative_quantity < 5, 0, if (@sup <> supplier, 1, @reached_five + 1)) as reached_five,

-- next takes note of changes in supplier being processed

@sup := if(@sup <> supplier, supplier, @sup) as sup

FROM

(

--this subquery is key for getting things in supplier order, by descending id

SELECT *

FROM `sample_table`

ORDER BY supplier, ID DESC

) reverse_order_by_id

INNER JOIN

(

-- initialize the variables used to their first ever values

SELECT @cumulative_quantity := 0, @sup := 0, @reached_five := 0

) only_here_to_initialize_variables

) t_alias

where reached_five <= 1 -- only get things up through the time we first get to 5 or above.

回答3:

Standard SQL has no concept of 'what row number am I up to', so this can only be implemented using something called a cursor. Writing code with cursors is something like writing code with for loops in other languages.

An example of how to use cursors is here:

http://dev.mysql.com/doc/refman/5.0/en/cursors.html

回答4:

Here is a rough demo about cursor, may be it's helpful.

CREATE TABLE #t

(

ID INT IDENTITY,

Supplier INT,

QTY INT

);

TRUNCATE TABLE #t;

INSERT INTO #t (Supplier, QTY)

VALUES (1, 2),

(1, 2),

(2, 5),

(3, 2),

(1, 3);

DECLARE @sum AS INT;

DECLARE @qty AS INT;

DECLARE @totalRows AS INT;

DECLARE curSelectQTY CURSOR

FOR SELECT QTY

FROM #t

ORDER BY QTY DESC;

OPEN curSelectQTY;

SET @sum = 0;

SET @totalRows = 0;

FETCH NEXT FROM curSelectQTY INTO @qty;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sum = @sum + @qty;

SET @totalRows = @totalRows + 1;

IF @sum >= 5

BREAK;

END

SELECT TOP (@totalRows) *

FROM #t

ORDER BY QTY DESC;

CLOSE curSelectQTY;

DEALLOCATE curSelectQTY;

回答5:

SELECT x.*

FROM supplier_stock x

JOIN supplier_stock y

ON y.supplier = x.supplier

AND y.id >= x.id

GROUP

BY supplier

, id

HAVING SUM(y.qty) <=5;

来源：https://stackoverflow.com/questions/14450660/select-rows-until-a-total-amount-is-met-in-a-column-mysql