在MySQL中，选择行直到某一列的总数达到特定值

Question

我在SF上看到过这个问题，但是作为一个新手，我无法理解它们。所以如果这感觉像重复，请原谅我。

我的样例表

--------------------------
ID   | 供应商     | 数量
--------------------------
1        1            2
2        1            2
3        2            5
4        3            2
5        1            3
6        2            4

我需要获取"UNTIL"（某供应商累计数量达到或超过5之前的）行，按照特定供应商ID降序排列。

例如，在此示例中，对于供应商1，将是ID为5和2的行。

    Id - 唯一主键
    Supplier - 外键，有另外一个供应商信息表。
    Qty - 双精度浮点数

- Ela Buwa

Answer 1

虽然不太美观，但我认为这样做可以完成任务，也许可以成为更简便的基础。请注意，我使用“伪”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.

- DWright

---

另外，注意您可以删除最外层的select以查看具有所有变量的SELECT正在积极执行的内容。这有助于了解它的作用。 - DWright

Answer 2

这样怎么样？使用两个变量。

SQLFIDDLE演示

查询：

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 |

- bonCodigo

---

@ElaBuwa请看一下并评论，order by id desc在与cumulative total >=5匹配方面不是很清晰。 - bonCodigo

嗨B，感谢您费心设置了一个sqlfiddle。由于这是一个库存/库存相关的表格，我需要它以DESC格式呈现。如果我需要获取1个供应商的行，这是否有效？整个“@”的事情有点新，现在正在进行一些谷歌搜索。再次感谢您。我一定会检查并更新该线程。 - Ela Buwa

如果您想要累计总数达到 5，那么它可以跟踪构成它的相关 id。否则，嗯...顺便说一下，以 @ 开头的每个单词都是一个变量 :) - bonCodigo

@ElaBuwa，看起来你需要获取<=5的数据，按照ID降序排列，然后选择supplier 1的ID 5, 2，试着操作一下并进行评论。顺便说一句，以@开头的每个单词都是一个变量 :) 然后你可以过滤掉任何总数不超过5的供应商。 - bonCodigo

在这个截图中，是否有可能获取5个交叉记录的总和数量和未交叉总和数量供应商记录的最大值记录？ - user3157334

@user3157334，你介意分享一下你的SQLFiddle吗？我不明白你所说的“交叉记录”是什么意思。你能否将字段重命名，并用纯文本和数字写出你的原始数据以及期望的结果？ - bonCodigo

Answer 3

这是一个关于光标的简单演示，也许会有所帮助。

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;

- Erxin

---

这个存储过程是否只检索大于或等于5的记录？ - user3157334

Answer 4

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;

- Strawberry

---

这几乎可以工作。目标是使qty >= 5。 - Russ Jackson

Answer 5

标准 SQL 没有“我到了第几行”的概念，因此只能使用称为游标的东西来实现。 使用游标编写代码类似于在其他语言中使用for循环编写代码。

以下是使用游标的示例：

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

- Patashu

---

嗨，Patashu，谢谢你的回复。如果我的问题让人困惑，我很抱歉。但是，我不需要获取行号。只需要整个行的所有内容。就像典型的查询一样。例如：我需要运行查询“SELECT * FROM mytable WHERE supplier=1 until the total qty >= 5 order by DESC”，之类的东西。只是不知道如何获取所有记录，直到总数大于等于5。如果我让它更加混乱，那我很抱歉。 - Ela Buwa

这个表有一个顺序键（它是一个自增整数，但也可以是由日期/时间戳和供应商ID组成的复合键），因此在这种情况下我们不受游标的限制。 - Strawberry