问题
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