“反向"指的是“反向".限制? [英] "Inverse" Limit?

问题描述

我正在使用MySQL存储财务资料，并使用数据来构建每个帐户的所有交易记录.出于性能方面的考虑-为了防止用户被庞大的表格所淹没-我对结果进行了分页.

I'm using MySQL to store financial stuff, and using the data to build, among other things, registers of all the transactions for each account. For performance reasons - and to keep the user from being overwhelmed by a gargantuan table - I paginate the results.

现在，作为注册的一部分，我将显示该帐户的余额.因此，如果我每页显示20个交易，并且显示第二页，则使用以下数据:

Now, as part of the register, I display a running balance for the account. So if I'm displaying 20 transactions per page, and I'm displaying the second page, I use the data as follows:

• 交易0-19::忽略它们-它们比正在查看的页面要新.
• 交易20-39::从中选择所有内容-它们将显示出来.
• 交易40-??:对这些金额进行求和，以便使余额保持准确.

• Transactions 0 - 19: Ignore them - they're more recent than the page being looked at.
• Transactions 20 - 39: Select everything from these - they'll be displayed.
• Transactions 40 - ??: Sum the amounts from these so the running balance is accurate.

最后一个让我很烦.使用LIMIT子句选择前 40个交易很容易，但是除了前40个交易以外，还有什么可比的东西吗?诸如"LIMIT -40"之类的东西?

It's that last one that's annoying me. It's easy to select the first 40 transactions using a LIMIT clause, but is there something comparable for everything but the first 40? Something like "LIMIT -40"?

我知道我可以用一个COUNT和一点数学就能做到这一点，但是实际的查询有点丑陋(多个JOIN和GROUP BY)，所以我宁愿尽可能少地发布它.这似乎足够有用，可以包含在SQL中-我只是不知道.还有其他人吗?

I know I can do this with a COUNT and a little math, but the actual query is a bit ugly (multiple JOINs and GROUP BYs), so I'd rather issue it as few times as possible. And this seems useful enough to be included in SQL - and I just don't know about it. Does anybody else?

推荐答案

文档说:

LIMIT子句可用于限制返回的行数 通过SELECT语句. LIMIT带有一个或两个数字参数， 它们都必须是非负整数常量 例外:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

• 在准备好的语句中，可以指定LIMIT参数 使用 ?占位符标记.

• Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

在存储的程序中，可以使用以下命令指定LIMIT参数 自MySQL起的整数值例程参数或局部变量 5.5.6.

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.

有两个参数，第一个参数指定 第一行返回，第二行指定最大行数 要返回的行.初始行的偏移量为0(而不是1):

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

要检索从特定偏移量到结果结尾的所有行 设置，您可以为第二个参数使用较大的数字.这 语句检索从第96行到最后一行的所有行:

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

下次，请使用该文档作为您的第一通电话.

Next time, please use the documentation as your first port of call.

这篇关于“反向"指的是“反向".限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！