选择行，直到列中满足总量（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.

我在SF看过这个问题,但我是一个菜鸟,我只是无法让我的油炸大脑在他们周围。如果这感觉像是重复,请原谅我。

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.

我需要获取行“UNTIL”,“QTY”的累计总数等于或大于5,按特定供应商ID的降序排列。

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

在此示例中,对于供应商1,它将是具有5和2的ID的行。

    Id - unique primary key
    Supplier - foreign key, there is another table for supplier info.
    Qty - double

5 个解决方案

#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.

它不漂亮,但我认为这样做,也许它可以成为不那么繁琐的东西的基础。请注意,我使用“假”INNER JOIN只是为了第一次初始化一些变量 - 它没有任何其他角色。

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.

标准SQL没有“我应该使用什么行号”的概念,因此只能使用称为游标的东西来实现。使用游标编写代码就像在其他语言中使用for循环编写代码一样。

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;

#1