SQL:选择“直到" [英] SQL: Select "until"

问题描述

我正在寻找一种选择方法，直到达到总和.

I'm looking for a way to select until a sum is reached.

我的文档"表具有"tag_id"和"size"字段.

My "documents" table has "tag_id" and "size" fields.

我想使用tag_id = 26选择所有文档，但是我知道我只能处理600个尺寸的单位.因此，当我知道前十个文档的总数已经超过600个时，选择100个文档并丢弃其中的90个文档是没有意义的.

I want to select all of the documents with tag_id = 26 but I know I can only handle 600 units of size. So, there's no point in selecting 100 documents and discarding 90 of them when I could have known that the first 10 already added up to > 600 units.

因此，目标是:当我要丢弃大部分数据时，不要带回大量数据进行解析.

So, the goal is: don't bring back a ton of data to parse through when I'm going to discard most of it.

...但是我也很想避免在此应用程序中引入使用游标的功能.

...but I'd also really like to avoid introducing working with cursors to this app.

我正在使用mysql.

I'm using mysql.

推荐答案

在累加最大单位时，您需要某种方式来排序哪些记录比其他记录具有优先权.否则，您如何知道您最多保留600条记录呢?

You need some way to order which records get priority over others when adding up to your max units. Otherwise, how do you know which set of records that totals up to 600 do you keep?

SELECT d.id, d.size, d.date_created
FROM documents d
INNER JOIN documents d2 ON d2.tag_id=d.tag_id AND d2.date_created >= d.date_created
WHERE d.tag_id=26
GROUP BY d.id, d.size, d.date_created
HAVING sum(d2.size) <= 600
ORDER BY d.date_created DESC

这只是一个入门的基本查询，还有许多问题需要解决:

This is just a basic query to get you started, and there are a number of problems still to solve:

• 它在< = 600处停止，因此在大多数情况下，您不会完全满足您的尺寸限制.这意味着您可能想要对其进行调整以允许再添加一条记录.例如，如果第一条记录> 600，则查询将不返回任何内容，这可能是一个问题.
• 稍后它不会采取任何措施来检查其他较小的记录，但这些记录可能仍在上限范围内.
• 具有相同date_created值的记录可能在这里和那里都是重复计算".

修改
由于他添加了按日期排序的信息，因此已更新.

edit
Updated since he added information that he's sorting by date.

这篇关于SQL:选择“直到"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！