Oracle兼容-语法-OFFSET … FETCH

1. 语法

  1. [ OFFSET offset { ROW | ROWS } ]
  2. [ FETCH { FIRST | NEXT } [ { row_count | percent PERCENT } ]
  3.     { ROW | ROWS } { ONLY | WITH TIES } ]

2. 定义和用法

下面是关于 SELECT ... OFFSET ... FETCH 用法的说明：

  • NEXT 和 FIRST 是具有相同意义的关键字。

  • ROW 和 ROWS 也是具有相同意义的关键字。

  • 关于 row_count | percent PERCENT

OFFSET 子句用于指定在行限制开始之前要跳过行数偏移量，OFFSET 子句是可选的。如果跳过它，则偏移量为0，行限制从第一行开始计算。

偏移量必须是一个数字或一个表达式（表达式计算结果值为一个数字）。偏移量遵守以下规则：

  1. 如果偏移量是负值，则将视为0。

  2. 如果偏移量为NULL或大于查询返回的行数，则不返回任何行。

  3. 如果 row_count 包含一个小数，则小数部分被截断。

  4. PERCENT 百分值返回表数值的百分比的行数，如果 percent 计算后的结果包含小数，则向上补一位。例如：表数据共100条，percent=0.1 时计算结果为 100_0.1/100=0.1*，含有小数则向上补一位为 _1。

  • 关于 ONLY | WITH TIES

  1. ONLY: 仅返回 FETCH NEXT/FIRST 后的行数或行数的百分比。

  2. WITH TIES：返回与 ORDER BY 最后一行相同值的记录 。注意：如果使用 WITH TIES，则必须在查询中指定一个 ORDER BY 子句。如果不这样做，查询将不会返回额外的行。

3. Oracle兼容说明

  1. 在Oracle中不可以和 FOR UPDATE 同时使用，但在GreatSQL中，允许 LIMIT OFFSET 与 FOR UPDATE 同时使用。

  2. 如果查询列表中包含了相同的名称的字段，则需要用别名区分。

  3. 在GreatSQL中，OFFSET 偏移量存在隐式转换可能，不同函数的隐式转换结果可能存在不一致。

4. 示例

  1. greatsql> CREATE TABLE t1 (a INT);
  2. greatsql> INSERT INTO t1 VALUES (1), (1), (2), (3), (2);
  4. greatsql> SELECT * FROM t1
  5. OFFSET 2 ROWS;
  6. +------+
  7. | a    |
  8. +------+
  9. |    2 |
  10. |    3 |
  11. |    2 |
  12. +------+
  13. 3 rows in set (0.00 sec)
  15. greatsql> SELECT * FROM t1
  16. FETCH FIRST ROW ONLY;
  17. +------+
  18. | a    |
  19. +------+
  20. |    1 |
  21. +------+
  22. 1 row in set (0.00 sec)
  24. greatsql> SELECT * FROM t1
  25. FETCH FIRST ROWS ONLY;
  26. +------+
  27. | a    |
  28. +------+
  29. |    1 |
  30. +------+
  31. 1 row in set (0.00 sec)
  33. greatsql> SELECT * FROM t1
  34. FETCH NEXT ROW ONLY;
  35. +------+
  36. | a    |
  37. +------+
  38. |    1 |
  39. +------+
  40. 1 row in set (0.00 sec)
  42. greatsql> SELECT * FROM t1
  43. FETCH NEXT ROWS ONLY;
  44. +------+
  45. | a    |
  46. +------+
  47. |    1 |
  48. +------+
  49. 1 row in set (0.00 sec)
  51. greatsql> SELECT * FROM t1 ORDER BY a
  52. OFFSET 2 ROW
  53. FETCH FIRST ROW ONLY;
  54. +------+
  55. | a    |
  56. +------+
  57. |    2 |
  58. +------+
  59. 1 row in set (0.00 sec)
  61. greatsql> SELECT * FROM t1 ORDER BY a
  62. OFFSET 2 ROWS
  63. FETCH FIRST 1 ROW ONLY;
  64. +------+
  65. | a    |
  66. +------+
  67. |    2 |
  68. +------+
  69. 1 row in set (0.00 sec)
  71. greatsql> SELECT * FROM t1
  72. FETCH FIRST ROW WITH TIES;
  73. +------+
  74. | a    |
  75. +------+
  76. |    1 |
  77. +------+
  78. 1 row in set (0.00 sec)
  80. greatsql> SELECT * FROM t1 ORDER BY a
  81. FETCH FIRST ROW WITH TIES;
  82. +------+
  83. | a    |
  84. +------+
  85. |    1 |
  86. |    1 |
  87. +------+
  88. 2 rows in set (0.00 sec)
  90. greatsql> SELECT * FROM t1 ORDER BY a
  91. OFFSET 2 ROWS
  92. FETCH FIRST ROW WITH TIES;
  93. +------+
  94. | a    |
  95. +------+
  96. |    2 |
  97. |    2 |
  98. +------+
  99. 2 rows in set (0.00 sec)
  101. greatsql> SELECT * FROM t1
  102. FETCH FIRST 3 ROW ONLY;
  103. +------+
  104. | a    |
  105. +------+
  106. |    1 |
  107. |    1 |
  108. |    2 |
  109. +------+
  110. 3 rows in set (0.00 sec)
  112. greatsql> SELECT * FROM t1
  113. FETCH NEXT 1+2 ROWS ONLY;
  114. +------+
  115. | a    |
  116. +------+
  117. |    1 |
  118. |    1 |
  119. |    2 |
  120. +------+
  121. 3 rows in set (0.00 sec)
  123. greatsql> SELECT * FROM t1 ORDER BY a
  124. OFFSET 2 ROWS FETCH FIRST 3 ROW ONLY;
  125. +------+
  126. | a    |
  127. +------+
  128. |    2 |
  129. |    2 |
  130. |    3 |
  131. +------+
  132. 3 rows in set (0.00 sec)
  134. greatsql> SELECT * FROM t1
  135. OFFSET 2 ROWS FETCH NEXT 3 ROW ONLY;
  136. +------+
  137. | a    |
  138. +------+
  139. |    2 |
  140. |    3 |
  141. |    2 |
  142. +------+
  143. 3 rows in set (0.00 sec)
  145. greatsql> SELECT * FROM t1 ORDER BY a
  146. FETCH FIRST 3 ROWS WITH TIES;
  147. +------+
  148. | a    |
  149. +------+
  150. |    1 |
  151. |    1 |
  152. |    2 |
  153. |    2 |
  154. +------+
  155. 4 rows in set (0.00 sec)
  157. greatsql> SELECT * FROM t1 ORDER BY a
  158. OFFSET 2 ROWS
  159. FETCH NEXT 3 ROWS WITH TIES;
  160. +------+
  161. | a    |
  162. +------+
  163. |    2 |
  164. |    2 |
  165. |    3 |
  166. +------+
  167. 3 rows in set (0.00 sec)
  169. greatsql> SELECT * FROM t1
  170. FETCH NEXT 49 PERCENT ROWS ONLY;
  171. +------+
  172. | a    |
  173. +------+
  174. |    1 |
  175. |    1 |
  176. |    2 |
  177. +------+
  178. 3 rows in set (0.00 sec)
  180. greatsql> SELECT * FROM t1
  181. FETCH NEXT 50 PERCENT ROWS ONLY;
  182. +------+
  183. | a    |
  184. +------+
  185. |    1 |
  186. |    1 |
  187. |    2 |
  188. +------+
  189. 3 rows in set (0.00 sec)
  191. greatsql> SELECT * FROM t1
  192. OFFSET 2 ROWS
  193. FETCH NEXT 50 PERCENT ROWS ONLY;
  194. +------+
  195. | a    |
  196. +------+
  197. |    2 |
  198. |    3 |
  199. |    2 |
  200. +------+
  201. 3 rows in set (0.00 sec)
  203. greatsql> SELECT * FROM t1
  204. FETCH NEXT 50 PERCENT ROWS WITH TIES;
  205. +------+
  206. | a    |
  207. +------+
  208. |    1 |
  209. |    1 |
  210. |    2 |
  211. +------+
  212. 3 rows in set (0.00 sec)
  214. greatsql> SELECT * FROM t1 ORDER BY a
  215. OFFSET 1 ROWS
  216. FETCH NEXT 49 PERCENT ROWS WITH TIES;
  217. +------+
  218. | a    |
  219. +------+
  220. |    1 |
  221. |    2 |
  222. |    2 |
  223. +------+
  224. 3 rows in set (0.00 sec)

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx