限制SQL查询结果的行值总和

Question

所以我有点困惑，在我的表格中设置了这样的结构

+-----------+------+
| Timestamp | Size |
+-----------+------+
|   1-1-13  + 10.3 +
+-----------+------+
|   1-3-13  +  6.7 +
+-----------+------+
|   1-5-13  +  3.0 +
+-----------+------+
|   1-9-13  + 11.4 +
+-----------+------+

我想知道是否有办法运行像这样的查询：

SELECT * FROM table ORDER BY timestamp ASC LIMIT BY (SUM(size) <= 20.0);

这将获取前三行，因为前三行中大小的总和是20。然而，并不总是等于20的3行。有时候第一行可能有一个值为20，在这种情况下，它应该只获取第一行。

我已经知道在查询运行后可以使用PHP快速计算总和，但我正在尝试仅使用MySQL来完成这个目标。

- garetmckinley

---

所以你想要一个运行总数？ - Hart CO

在 ANSI SQL 中，您可以根据每个输出行匹配的条件选择某个表（关系）的行。在您的示例中，每个新行的条件都会更改。不确定，但我认为它将需要 PL/SQL 函数。请注意 ORDER 的工作方式-取决于子查询的使用，您将获得不同的结果，具体取决于您想要输出进行排序还是输入符合您条件的内容，并且 ORDER 不应该像这样使用。LIMIT（或OFFSET）可能应该用于简单地限制输出行数，而不是通过表达式进行限制。 - Piotr Wadas

Answer 1

 SELECT * FROM ints ORDER BY i;
 +---+
 | i |
 +---+
 | 0 |
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 5 |
 | 6 |
 | 7 |
 | 8 |
 | 9 |
 +---+

 SELECT x.* ,SUM(y.i) FROM ints x JOIN ints y ON y.i <= x.i GROUP BY x.i;
 +---+----------+
 | i | SUM(y.i) |
 +---+----------+
 | 0 |        0 |
 | 1 |        1 |
 | 2 |        3 |
 | 3 |        6 |
 | 4 |       10 |
 | 5 |       15 |
 | 6 |       21 |
 | 7 |       28 |
 | 8 |       36 |
 | 9 |       45 |
 +---+----------+

 SELECT x.* ,SUM(y.i) FROM ints x JOIN ints y ON y.i <= x.i GROUP BY x.i HAVING SUM(y.i) <= 20;
 +---+----------+
 | i | SUM(y.i) |
 +---+----------+
 | 0 |        0 |
 | 1 |        1 |
 | 2 |        3 |
 | 3 |        6 |
 | 4 |       10 |
 | 5 |       15 |
 +---+----------+

- Strawberry

---

我试图通过子查询来解决这个问题，但是使用连接更好和更易读。+1 - Marcassin

通过变量保存运行总数可以避免自连接的开销。 - Hart CO

我相信在MSSQL中肯定有，但无法确认MySQL的性能是否会提高。 - Hart CO

@iGARET 是的。如果性能看起来不太好，请考虑使用 Goat_CO 的解决方案。 - Strawberry

+1，希望能够更多 - 绝对精彩的查询和很好的解释！ - Ed Gibbs

Answer 2

您想要添加一个运行总数，并基于此进行限制，以下内容应该有效：

SET @runtot:=0;
 SELECT 
    q1.t,
    q1.s,
    (@runtot := @runtot + q1.s) AS rt
 FROM 
    (SELECT Date AS t,
     SIZE AS s
     FROM  Table1
     ORDER  BY Date
     ) AS q1
WHERE @runtot + q1.s <= 20

Edit: Demo here - SQL Fiddle

- Hart CO

---

经过一点研究，结果证明你是正确的。计算运行总数比连接更有效。感谢您的回答！ - garetmckinley

如果只有 size > 20 的项目会发生什么？如果查询没有结果，您能建议如何更改查询以获取至少 20 的总大小，并在总数大于或等于 20 时停止获取更多行吗？ - Serge Rogatch

Answer 3

使用窗口函数（在MySQL 8.0+和MariaDB 10.2+中）：

SELECT *
FROM (
  SELECT t.*, SUM(size) OVER (
    PARTITION by NULL
    ORDER BY t.Timestamp
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as csum
  FROM MyTable t
  ORDER BY t.Timestamp
) x
WHERE csum <= 20;

或者更短：

SELECT *
FROM (
  SELECT t.*, SUM(size) OVER (ORDER BY t.Timestamp) as csum
  FROM MyTable t
  ORDER BY t.Timestamp
) x
WHERE csum <= 20;

在db-fiddle.com上的演示

- Paul Spiegel