MySQL的总和限制 [英] MySQL limit by sum

问题描述

我想按总数限制mySQL中的SELECT结果. 例如，这是我的表格:

I want to limit my SELECT results in mySQL by sum. For Example, this is my table:

(id, val)

数据条目: (1,100)， (2,300)， (3,50)， (4,3000)

Data Entries: (1,100), (2,300), (3,50), (4,3000)

我想选择头个条目，以使这些条目中的 val 之和足以使其达到M. 例如，我要查找的条目使得M = 425. 结果应为(1,100)，(2,300)，(3,50).

I want to select first k entries such that the sum of val in those entries is just enough to make it to M. For example, I want to find entries such that M = 425. The result should be (1,100),(2,300),(3,50).

如何在mysql选择查询中做到这一点?

How can I do that in a mysql select query?

推荐答案

尝试使用此变体-

SET @sum = 0;
SELECT id, val FROM (
  SELECT *, @sum:=@sum + val mysum FROM mytable2 ORDER BY id
) t
WHERE mysum <= 450;

+------+------+
| id   | val  |
+------+------+
|    1 |  100 |
|    2 |  300 |
|    3 |   50 |
+------+------+

这篇关于MySQL的总和限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！