Oracle兼容-语法-OFFSET … FETCH

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，行限制从第一行开始计算。

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

如果偏移量是负值，则将视为0。
如果偏移量为NULL或大于查询返回的行数，则不返回任何行。
如果 row_count 包含一个小数，则小数部分被截断。
PERCENT 百分值返回表数值的百分比的行数，如果 percent 计算后的结果包含小数，则向上补一位。例如：表数据共100条，percent=0.1 时计算结果为 100_0.1/100=0.1*，含有小数则向上补一位为 _1。
关于 ONLY | WITH TIES
ONLY: 仅返回 FETCH NEXT/FIRST 后的行数或行数的百分比。
WITH TIES：返回与 ORDER BY 最后一行相同值的记录。注意：如果使用 WITH TIES，则必须在查询中指定一个 ORDER BY 子句。如果不这样做，查询将不会返回额外的行。

3. Oracle兼容说明

在Oracle中不可以和 FOR UPDATE 同时使用，但在GreatSQL中，允许 LIMIT OFFSET 与 FOR UPDATE 同时使用。
如果查询列表中包含了相同的名称的字段，则需要用别名区分。
在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)