SQL 使用 LIMIT/OFFSET 运行查询并获取总行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28888375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run a query with a LIMIT/OFFSET and also get the total number of rows
提问by Tim
For pagination purposes, I need a run a query with the LIMITand OFFSETclauses. But I also need a count of the number of rows that would be returned by that query without the LIMITand OFFSETclauses.
出于分页目的，我需要使用LIMITandOFFSET子句运行查询。但是我还需要一个没有LIMITandOFFSET子句的查询将返回的行数的计数。
I want to run:
我想跑：
SELECT * FROM table WHERE /* whatever */ ORDER BY col1 LIMIT ? OFFSET ?
And:
和：
SELECT COUNT(*) FROM table WHERE /* whatever */
At the same time. Is there a way to do that, particularly a way that lets Postgres optimize it, so that it's faster than running both individually?
同时。有没有办法做到这一点，特别是一种让 Postgres 优化它的方法，以便它比单独运行更快？
回答by Erwin Brandstetter
Yes.With a simple window function:
是的。使用简单的窗口函数：
SELECT *, count(*) OVER() AS full_count
FROM tbl
WHERE /* whatever */
ORDER BY col1
OFFSET ?
LIMIT ?Be aware that the cost will be substantially higher than without the total number, but typically still cheaper than two separate queries. Postgres has to actually count all rowseither way, which imposes a cost depending on the total number of qualifying rows. Details:
请注意，成本将大大高于没有总数的情况，但通常仍比两个单独的查询便宜。无论哪种方式，Postgres 都必须实际计算所有行，这会根据符合条件的行的总数产生成本。细节：
However, as Dani pointed out, when OFFSETis at least as great as the number of rows returned from the base query, no rows are returned. So we also don't get full_count.
但是，正如 Dani 指出的那样，当OFFSET至少与从基本查询返回的行数一样大时，不会返回任何行。所以我们也没有得到full_count.
If that's not acceptable, a possible workaround to always return the full countwould be with a CTE and an OUTER JOIN:
如果这是不可接受的，始终返回完整计数的可能解决方法是使用 CTE 和OUTER JOIN：
WITH cte AS (
SELECT *
FROM tbl
WHERE /* whatever */
)
SELECT *
FROM (
TABLE cte
ORDER BY col1
LIMIT ?
OFFSET ?
) sub
RIGHT JOIN (SELECT count(*) FROM cte) c(full_count) ON true;
You get one row of NULL values with the full_countappended if OFFSETis too big. Else, it's appended to every row like in the first query.
您会得到一行 NULL 值并full_count附加 ifOFFSET太大。否则，它会像第一个查询一样附加到每一行。
If a row with all NULL values is a possible valid result you have to check offset >= full_countto disambiguate the origin of the empty row.
如果具有所有 NULL 值的行是可能的有效结果，则必须检查offset >= full_count以消除空行的来源的歧义。
This still executes the base query only once. But it adds more overhead to the query and only pays if that's less than repeating the base query for the count.
这仍然只执行一次基本查询。但它会增加查询的开销，并且仅在少于重复计数的基本查询时才付费。
If indexes supporting the final sort order are available, it might pay to include the ORDER BYin the CTE (redundantly).
如果支持最终排序顺序的索引可用，则可能需要将 包含ORDER BY在 CTE 中（冗余）。
回答by Fran?ois Gueguen
edit: this answer is valid when retrieving the unfiltered table. I'll let it in case it could help someone but it might not exactly answer the initial question.
编辑：检索未过滤的表时，此答案有效。我会放手，以防它可以帮助某人，但它可能无法完全回答最初的问题。
Erwin Brandstetter's answer is perfect if you need an accurate value. However, on large tables you often only need a pretty good approximation. Postgres gives you just thatand it will be much faster as it will not need to evaluate each row:
如果您需要准确的值，Erwin Brandstetter的答案是完美的。但是，在大型表上，您通常只需要一个非常好的近似值。Postgres就是这样，它会更快，因为它不需要评估每一行：
SELECT *
FROM (
SELECT *
FROM tbl
WHERE /* something */
ORDER BY /* something */
OFFSET ?
LIMIT ?
) data
RIGHT JOIN (SELECT reltuples FROM pg_class WHERE relname = 'tbl') pg_count(total_count) ON true;
I'm actually quite not sure if there is an advantage to externalize the RIGHT JOINor have it as in a standard query. It would deserve some testing.
我实际上不太确定是否有优势将其外部化RIGHT JOIN或在标准查询中使用它。它值得一些测试。
SELECT t.*, pgc.reltuples AS total_count
FROM tbl as t
RIGHT JOIN pg_class pgc ON pgc.relname = 'tbl'
WHERE /* something */
ORDER BY /* something */
OFFSET ?
LIMIT ?
回答by Mohd Rashid
Its bad practice to call two times same query for Just to get the total number of rows of the returend result. It will take execution time and will waste the server resource.
为 Just 调用两次相同的查询以获取返回结果的总行数是不好的做法。这将花费执行时间并且会浪费服务器资源。
Better, you can use SQL_CALC_FOUND_ROWSin the query which will tell the MySQL to fetch the total number of row count along with the limit query results.
更好的是，您可以SQL_CALC_FOUND_ROWS在查询中使用它会告诉 MySQL 获取行计数的总数以及限制查询结果。
Example set as:
示例设置为：
SELECT SQL_CALC_FOUND_ROWS employeeName, phoneNumber FROM employee WHERE employeeName LIKE 'a%' LIMIT 10;
SELECT FOUND_ROWS();
In the above Query, Just add SQL_CALC_FOUND_ROWSoption in the rest required query and execute the second line i.e. SELECT FOUND_ROWS()returns the number of rows in the result set returned by that statement.
在上面的查询中，只需SQL_CALC_FOUND_ROWS在其余所需的查询中添加选项并执行第二行，即SELECT FOUND_ROWS()返回该语句返回的结果集中的行数。
回答by Richard Huxton
No.
不。
There's perhaps some small gain you could theoretically gain over running them individually with enough complicated machinery under the hood. But, if you want to know how many rows match a condition you'll have to count them rather than just a LIMITed subset.
理论上，通过在引擎盖下使用足够复杂的机器单独运行它们，您可能会获得一些小收益。但是，如果您想知道有多少行符合条件，则必须计算它们而不仅仅是有限的子集。
