使用LIMIT / OFFSET运行查询，并获得总行数

锄奸在线

出于分页目的，我需要使用LIMIT和OFFSET子句运行查询。但是我还需要计算不带LIMITandOFFSET子句的查询将返回的行数。
- O5 c2 w0 @5 W9 |( q, k我要跑步：
SELECT * FROM table WHERE /* whatever */ ORDER BY col1 LIMIT ? OFFSET ?
和：
SELECT COUNT(*) FROM table WHERE /* whatever */
同时。有没有办法做到这一点，特别是让Postgres优化它的方法，使其比单独运行它们都快？
" `  t9 G1 L+ ?8 d6 f9 m! c               
解决方案:
- q. C9 n: T6 C               

- L8 o& ]7 c5 v8 N
                是的。 具有简单的窗口功能：
SELECT *, **count(*) OVER() AS full_count**
( y# Q1 r) r$ {: UFROM   tbl
% k! F6 J9 X1 z/ |3 ^2 V9 h! l' kWHERE  /* whatever */
ORDER  BY col1
) Z" X- i  n, I1 c  r; B+ SOFFSET ?
3 O5 r) Y: S7 H) C: d; q" {LIMIT  ?
) T" o" }5 K+ V9 E$ m, ^请注意，成本将比没有总数大得多，但通常仍比两个单独的查询便宜。Postgres必须实际上以任何一种方式对 所有行 进行 计数
，这取决于符合条件的行的总数而产生了费用。
但是 ，正如Dani所指出的那样，当OFFSET至少等于从基本查询返回的行数时，将不返回任何行。所以我们也没有得到full_count。
7 B% G  ]+ b4 K7 U  R& N( ]8 |* r" n如果不可接受，则 始终返回全部计数 的可能 解决方法 是CTE和OUTER JOIN：
$ h- O; T- D/ o7 ^WITH cte AS (
% S$ L* |: q; N9 |0 }$ e& @   SELECT *
# ~, b( z' S3 d2 c' S! ]& i6 F. I7 Y   FROM   tbl
* f: A, U+ V! t) L. b   WHERE  /* whatever */
   )
SELECT *
7 A4 d. K& z/ n3 z. j7 ?8 ^6 z+ H  WFROM  (
   TABLE  cte
5 C8 V9 y/ V9 w7 n   ORDER  BY col1
   LIMIT  ?
* [8 y- r$ K8 W3 G# o6 `0 L; x   OFFSET ?
  o% b  F) `: a* p) M   ) sub
: H8 @4 o0 p" `+ ~8 w& c0 A+ D. PRIGHT  JOIN (SELECT count(*) FROM cte) c(full_count) ON true;
如果行太大，full_count则会得到一行NULL值，并附加OFFSET。否则，它会像第一个查询一样附加到每一行。
- t7 p* ~: L4 H* d, c! {' z/ H如果具有所有NULL值的行是可能的有效结果，则必须检查offset >= full_count以消除空行的来源的歧义。
/ K/ W5 M0 b8 ^这仍然只执行一次基本查询。但这会增加查询的开销，并且只有在比重复进行基本查询的次数少的情况下才需要付费。
  ~% n% p* B7 }/ G' B如果支持最终排序顺序的索引可用，则可能需要将其包括ORDER BY在CTE中（冗余）。