LIMIT 子句
說明
LIMIT 子句用於限制 SELECT 陳述式傳回的列數。一般而言，此子句會與 ORDER BY 搭配使用，以確保結果具有確定性。
語法
LIMIT { ALL | integer_expression }
參數
-
ALL
如果指定，查詢會傳回所有列。換句話說，如果指定此選項，則不套用任何限制。
-
integer_expression
指定傳回整數的可摺疊運算式。
範例
CREATE TABLE person (name STRING, age INT);
INSERT INTO person VALUES
('Zen Hui', 25),
('Anil B', 18),
('Shone S', 16),
('Mike A', 25),
('John A', 18),
('Jack N', 16);
-- Select the first two rows.
SELECT name, age FROM person ORDER BY name LIMIT 2;
+------+---+
| name|age|
+------+---+
|Anil B| 18|
|Jack N| 16|
+------+---+
-- Specifying ALL option on LIMIT returns all the rows.
SELECT name, age FROM person ORDER BY name LIMIT ALL;
+-------+---+
| name|age|
+-------+---+
| Anil B| 18|
| Jack N| 16|
| John A| 18|
| Mike A| 25|
|Shone S| 16|
|Zen Hui| 25|
+-------+---+
-- A function expression as an input to LIMIT.
SELECT name, age FROM person ORDER BY name LIMIT length('SPARK');
+-------+---+
| name|age|
+-------+---+
| Anil B| 18|
| Jack N| 16|
| John A| 18|
| Mike A| 25|
|Shone S| 16|
+-------+---+
-- A non-foldable expression as an input to LIMIT is not allowed.
SELECT name, age FROM person ORDER BY name LIMIT length(name);
org.apache.spark.sql.AnalysisException: The limit expression must evaluate to a constant value ...