GreatSQL用户手册

§ Oracle兼容-语法-OFFSET ... FETCH

§ 1. 语法

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

§ 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 时计算结果为 1000.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. 示例

greatsql> CREATE TABLE t1 (a INT);
greatsql> INSERT INTO t1 VALUES (1), (1), (2), (3), (2);

greatsql> SELECT * FROM t1
OFFSET 2 ROWS;
+------+
| a    |
+------+
|    2 |
|    3 |
|    2 |
+------+
3 rows in set (0.00 sec)

greatsql> SELECT * FROM t1
FETCH FIRST ROW ONLY;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

greatsql> SELECT * FROM t1
FETCH FIRST ROWS ONLY;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

greatsql> SELECT * FROM t1
FETCH NEXT ROW ONLY;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

greatsql> SELECT * FROM t1
FETCH NEXT ROWS ONLY;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

greatsql> SELECT * FROM t1 ORDER BY a
OFFSET 2 ROW
FETCH FIRST ROW ONLY;
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

greatsql> SELECT * FROM t1 ORDER BY a
OFFSET 2 ROWS
FETCH FIRST 1 ROW ONLY;
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

greatsql> SELECT * FROM t1
FETCH FIRST ROW WITH TIES;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

greatsql> SELECT * FROM t1 ORDER BY a
FETCH FIRST ROW WITH TIES;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

greatsql> SELECT * FROM t1 ORDER BY a
OFFSET 2 ROWS
FETCH FIRST ROW WITH TIES;
+------+
| a    |
+------+
|    2 |
|    2 |
+------+
2 rows in set (0.00 sec)

greatsql> SELECT * FROM t1
FETCH FIRST 3 ROW ONLY;
+------+
| a    |
+------+
|    1 |
|    1 |
|    2 |
+------+
3 rows in set (0.00 sec)

greatsql> SELECT * FROM t1
FETCH NEXT 1+2 ROWS ONLY;
+------+
| a    |
+------+
|    1 |
|    1 |
|    2 |
+------+
3 rows in set (0.00 sec)

greatsql> SELECT * FROM t1 ORDER BY a
OFFSET 2 ROWS FETCH FIRST 3 ROW ONLY;
+------+
| a    |
+------+
|    2 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

greatsql> SELECT * FROM t1
OFFSET 2 ROWS FETCH NEXT 3 ROW ONLY;
+------+
| a    |
+------+
|    2 |
|    3 |
|    2 |
+------+
3 rows in set (0.00 sec)

greatsql> SELECT * FROM t1 ORDER BY a
FETCH FIRST 3 ROWS WITH TIES;
+------+
| a    |
+------+
|    1 |
|    1 |
|    2 |
|    2 |
+------+
4 rows in set (0.00 sec)

greatsql> SELECT * FROM t1 ORDER BY a
OFFSET 2 ROWS
FETCH NEXT 3 ROWS WITH TIES;
+------+
| a    |
+------+
|    2 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

greatsql> SELECT * FROM t1
FETCH NEXT 49 PERCENT ROWS ONLY;
+------+
| a    |
+------+
|    1 |
|    1 |
|    2 |
+------+
3 rows in set (0.00 sec)

greatsql> SELECT * FROM t1
FETCH NEXT 50 PERCENT ROWS ONLY;
+------+
| a    |
+------+
|    1 |
|    1 |
|    2 |
+------+
3 rows in set (0.00 sec)

greatsql> SELECT * FROM t1
OFFSET 2 ROWS
FETCH NEXT 50 PERCENT ROWS ONLY;
+------+
| a    |
+------+
|    2 |
|    3 |
|    2 |
+------+
3 rows in set (0.00 sec)

greatsql> SELECT * FROM t1
FETCH NEXT 50 PERCENT ROWS WITH TIES;
+------+
| a    |
+------+
|    1 |
|    1 |
|    2 |
+------+
3 rows in set (0.00 sec)

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

greatsql-wx