查询语句可扫描一个或多个表或表达式,并返回计算的结果行。本主题介绍 Cloud Spanner SQL 中的 SQL 查询语法。
SQL 语法
query_statement: [ statement_hint_expr ][ table_hint_expr ][ join_hint_expr ]
query_expr statement_hint_expr: '@{' statement_hint_key = statement_hint_value [, ...] '}' statement_hint_key: { USE_ADDITIONAL_PARALLELISM | OPTIMIZER_VERSION | LOCK_SCANNED_RANGES } query_expr: [ WITH with_query_name AS ( query_expr ) [, ...] ] { select | ( query_expr ) | query_expr set_op query_expr } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ] select: SELECT [ AS { STRUCT | VALUE } ] [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...] [ FROM from_item [ tablesample_type ] [, ...] ] [ WHERE bool_expression ] [ GROUP BY expression [, ...] ] [ HAVING bool_expression ] set_op: UNION { ALL | DISTINCT } | INTERSECT { ALL | DISTINCT } | EXCEPT { ALL | DISTINCT } from_item: { table_name [ table_hint_expr ] [ [ AS ] alias ] | join_operation | ( query_expr ) [ table_hint_expr ] [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ table_hint_expr ] [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ table_hint_expr ] [ [ AS ] alias ] } table_hint_expr: '@{' table_hint_key = table_hint_value '}' table_hint_key: { FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION } tablesample_type: TABLESAMPLE sample_method (sample_size percent_or_rows ) sample_method: { BERNOULLI | RESERVOIR } sample_size: numeric_value_expression percent_or_rows: { PERCENT | ROWS }
表示法规则
- 方括号“[ ]”表示可选的子句。
- 圆括号“( )”表示文本括号。
- 竖线“|”表示逻辑“或”(OR) 。
- 大括号“{ }”括起一组选项。
- 方括号内后跟省略号的逗号“[, …]”表示可以在逗号分隔列表中重复其前面的项目。
语句提示
查询语句支持以下提示:
提示键 | 可能的值 | 说明 |
---|---|---|
USE_ADDITIONAL_PARALLELISM |
TRUE FALSE (默认值) |
如果值为 TRUE ,则执行引擎将尽可能使用较高的并行性。由于此值可能会减少其他操作可用的资源,因此,如果您在同一实例上运行对延迟时间敏感的操作,则可能需要避免此提示。 |
OPTIMIZER_VERSION | 1 至 N|latest | 使用指定的优化器版本执行查询。可能的值包括 1 到 N (最新的优化工具版本)或 latest 。如果未设置提示,则优化器将针对在数据库选项中设置或通过客户端 API 指定的版本执行。如果二者均未设置,则优化器默认为最新版本。就版本设置偏好而言,客户端 API 设置的值优先于数据库选项中的值,而此提示设置的值优先于其他所有设置。 如需了解详情,请参阅查询优化器。 |
LOCK_SCANNED_RANGES |
exclusive shared (默认值) |
您可以根据此提示对由一个事务扫描的一组范围请求独占锁定。当您注意到高写入争用情况(也就是说,多个事务同时尝试读取和写入相同的数据)会导致大量中止时,获取独占锁定会很有帮助。 如果没有此提示,就可能会出现如下情况:多个并发事务获取共享锁定,然后尝试升级到独占锁定。这会导致死锁,因为每个事务的共享锁定会阻止其他事务升级到独占锁定。Cloud Spanner 会只留下一个事务,而中止所有其他事务。使用此提示请求独占锁定时,一个事务会获取锁定并继续执行,而其他事务则会等待轮到其锁定。吞吐量仍然受到限制,因为有冲突的事务一次只能执行一个,但是在这种情况下,Cloud Spanner 始终在一个事务上取得进展,从而节省了中止和重试事务的时间。 所有语句类型(查询和 DML)都支持此提示。 Cloud Spanner 始终会强制执行可序列化。锁定模式提示可能会影响争用工作负载中的哪些事务将等待或中止,但不会更改隔离级别。 由于这只是一个提示,它不应被视为等效于互斥锁。换句话说,您不应将 Cloud Spanner 独占锁定作为在 Cloud Spanner 外部执行代码的互斥机制。 如需了解详情,请参阅锁定。 |
示例表
下表用于说明本参考文档中不同查询子句的行为。
Roster 表
Roster
表列出了运动员姓氏 (LastName
) 以及分配给其学校的唯一 ID (SchoolID
)。如下所示:
+-----------------------+
| LastName | SchoolID |
+-----------------------+
| Adams | 50 |
| Buchanan | 52 |
| Coolidge | 52 |
| Davis | 51 |
| Eisenhower | 77 |
+-----------------------+
您可以使用此 WITH
子句为本参考文档中的示例模拟临时表名称:
WITH Roster AS
(SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
SELECT 'Buchanan', 52 UNION ALL
SELECT 'Coolidge', 52 UNION ALL
SELECT 'Davis', 51 UNION ALL
SELECT 'Eisenhower', 77)
SELECT * FROM Roster
PlayerStats 表
PlayerStats
表列出了运动员姓氏 (LastName
)、分配给给定比赛中的竞争对手的唯一 ID (OpponentID
),以及运动员在该比赛中的得分 (PointsScored
)。
+----------------------------------------+
| LastName | OpponentID | PointsScored |
+----------------------------------------+
| Adams | 51 | 3 |
| Buchanan | 77 | 0 |
| Coolidge | 77 | 1 |
| Adams | 52 | 4 |
| Buchanan | 50 | 13 |
+----------------------------------------+
您可以使用此 WITH
子句为本参考文档中的示例模拟临时表名称:
WITH PlayerStats AS
(SELECT 'Adams' as LastName, 51 as OpponentID, 3 as PointsScored UNION ALL
SELECT 'Buchanan', 77, 0 UNION ALL
SELECT 'Coolidge', 77, 1 UNION ALL
SELECT 'Adams', 52, 4 UNION ALL
SELECT 'Buchanan', 50, 13)
SELECT * FROM PlayerStats
TeamMascot 表
TeamMascot
表列出了唯一学校 ID (SchoolID
) 以及相应学校的吉祥物 (Mascot
)。
+---------------------+
| SchoolID | Mascot |
+---------------------+
| 50 | Jaguars |
| 51 | Knights |
| 52 | Lakers |
| 53 | Mustangs |
+---------------------+
您可以使用此 WITH
子句为本参考文档中的示例模拟临时表名称:
WITH TeamMascot AS
(SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL
SELECT 51, 'Knights' UNION ALL
SELECT 52, 'Lakers' UNION ALL
SELECT 53, 'Mustangs')
SELECT * FROM TeamMascot
SELECT 列表
SELECT [ AS { typename | STRUCT | VALUE } ] [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...]
SELECT
列表定义查询将返回的列。SELECT
列表中的表达式可以引用该列表相应的 FROM
子句的任何 from_item
中的列。
SELECT
列表中的每一项可以是以下其中之一:
*
expression
expression.*
SELECT *
SELECT *
通常称为查询星号,会对执行完整查询后可见的每个列生成一个输出列。
SELECT * FROM (SELECT "apple" AS fruit, "carrot" AS vegetable);
+-------+-----------+
| fruit | vegetable |
+-------+-----------+
| apple | carrot |
+-------+-----------+
SELECT expression
SELECT
列表中的项目可以是表达式。这些表达式的计算结果为单个值,并且生成一个输出列,其中具有可选的显式 alias
。
如果表达式没有显式别名,则在可能的情况下,其将根据隐式别名的规则接收隐式别名。 否则,该列是匿名的,您无法在查询的其他位置通过名称引用该列。
SELECT expression.*
SELECT
列表中的项也可以采用 expression.*
的形式。这会为每个列或 expression
的顶级字段生成一个输出列。表达式必须是表别名或者其计算结果是带有字段的数据类型(如 STRUCT)的单个值。
以下查询为表 groceries
中的每一列生成一个输出列,别名为 g
。
WITH groceries AS
(SELECT "milk" AS dairy,
"eggs" AS protein,
"bread" AS grain)
SELECT g.*
FROM groceries AS g;
+-------+---------+-------+
| dairy | protein | grain |
+-------+---------+-------+
| milk | eggs | bread |
+-------+---------+-------+
更多示例:
WITH locations AS
(SELECT STRUCT("Seattle" AS city, "Washington" AS state) AS location
UNION ALL
SELECT STRUCT("Phoenix" AS city, "Arizona" AS state) AS location)
SELECT l.location.*
FROM locations l;
+---------+------------+
| city | state |
+---------+------------+
| Seattle | Washington |
| Phoenix | Arizona |
+---------+------------+
WITH locations AS
(SELECT ARRAY<STRUCT<city STRING, state STRING>>[("Seattle", "Washington"),
("Phoenix", "Arizona")] AS location)
SELECT l.LOCATION[offset(0)].*
FROM locations l;
+---------+------------+
| city | state |
+---------+------------+
| Seattle | Washington |
+---------+------------+
* 运算符的修饰符
SELECT * EXCEPT
SELECT * EXCEPT
语句指定要从结果中排除的一个或多个列的名称。输出中将忽略所有匹配的列名称。
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * EXCEPT (order_id)
FROM orders;
+-----------+----------+
| item_name | quantity |
+-----------+----------+
| sprocket | 200 |
+-----------+----------+
SELECT * REPLACE
SELECT * REPLACE
语句指定一个或多个 expression AS identifier
子句。每个标识符必须与 SELECT *
语句中的列名匹配。在输出列列表中,该 REPLACE
子句中的表达式将替换与 REPLACE
子句中的标识符匹配的列。
SELECT * REPLACE
语句不会更改列的名称或顺序。但是该语句可以更改值和值类型。
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * REPLACE ("widget" AS item_name)
FROM orders;
+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5 | widget | 200 |
+----------+-----------+----------+
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * REPLACE (quantity/2 AS quantity)
FROM orders;
+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5 | sprocket | 100 |
+----------+-----------+----------+
重复行处理
您可以修改从 SELECT
查询返回的结果,如下所示。
SELECT DISTINCT
SELECT DISTINCT
语句会丢弃重复行并仅返回剩余行。SELECT DISTINCT
无法返回以下类型的列:
STRUCT
ARRAY
SELECT ALL
SELECT ALL
语句返回包括重复行在内的所有行。SELECT ALL
是 SELECT
的默认行为。
将 STRUCT 与 SELECT 搭配使用
Cloud Spanner API 不支持在返回类型的根目录下返回
STRUCT
的查询。例如,仅作为子查询时,以下查询才受到支持:SELECT STRUCT(1, 2) FROM Users;
支持返回结构体数组。例如,Cloud Spanner API 支持以下查询:
SELECT ARRAY(SELECT STRUCT(1 AS A, 2 AS B)) FROM Users;
SELECT ARRAY(SELECT AS STRUCT 1 AS a, 2 AS b) FROM Users;
但是,Cloud Spanner API 不支持返回
ARRAY<STRUCT<...>>
类型的NULL
值或带有NULL
元素的ARRAY<STRUCT<...>>
类型的值的查询形状,因此仅作为子查询时,以下查询才受到支持:SELECT ARRAY(SELECT IF(STARTS_WITH(Users.username, "a"), NULL, STRUCT(1, 2))) FROM Users;
如需查看如何在 ARRAY
中查询 STRUCTs
的更多示例,请参阅查询 ARRAY 中的 STRUCT 元素。
另请参阅有关如何在子查询中使用 STRUCTs
的说明。
值表
在 Cloud Spanner SQL 中,值表指的是行类型为单个值的表。在常规表中,每行由列组成,每列都有名称和类型。在值表中,行类型只是一个值,并且没有列名。
在 Cloud Spanner 中,值表主要作为 UNNEST
运算符或子查询的输出出现。如果使用的子查询生成某个值表,则 WITH
子句会引入一个值表。Cloud Spanner 不支持将值表用作数据库架构中的基表,并且不支持在查询结果中返回值表。因此,不支持将值表生成查询作为顶级查询。
如果需要使用恰好具有一列的查询,可以改用值表查询。例如,标量子查询和数组子查询(请参阅子查询)通常需要单列查询,但在 Cloud Spanner SQL 中,它们还允许使用值表查询。
如果查询使用 SELECT AS
,则会使用以下语法之一生成值表:
SELECT AS STRUCT
SELECT AS STRUCT expr [[AS] struct_field_name1] [,...]
此语法会生成一个具有 STRUCT 行类型的值表,其中 STRUCT 字段名称和类型与 SELECT
列表中生成的列名称和类型相匹配。
例如:
SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)
可以在标量或数组子查询中使用 SELECT AS STRUCT
生成将多个值组合在一起的单个 STRUCT 类型。此语法通常不允许标量和数组子查询(请参阅子查询)返回多个列,但可以返回 STRUCT 类型的单个列。
此处允许使用匿名列。
例如:
SELECT AS STRUCT 1 x, 2, 3
上面的查询会生成类型为 STRUCT<int64 x, int64, int64>.
的 STRUCT 值。第一个字段的名称为 x
,第二个和第三个字段是匿名字段。
上面的示例使用 STRUCT 构造函数生成与此 SELECT AS VALUE
查询相同的结果:
SELECT AS VALUE STRUCT(1 AS x, 2, 3)
允许出现重复的列。
例如:
SELECT AS STRUCT 1 x, 2 y, 3 x
上面的查询会生成类型为 STRUCT<int64 x, int64 y, int64 x>.
的 STRUCT 值。第一个字段和第三个字段具有相同的名称 x
,而第二个字段的名称为 y
。
上面的示例使用 STRUCT 构造函数生成与此 SELECT AS VALUE
查询相同的结果:
SELECT AS VALUE STRUCT(1 AS x, 2 AS y, 3 AS x)
SELECT AS VALUE
SELECT AS VALUE
从任何只生成一列的 SELECT
列表生成一个值表。输出将是一个值表,而不是生成具有一列的输出表(可能具有名称),其中行类型只是在一个 SELECT
列中生成的值类型。该列所具有的任何别名都将在值表中被舍弃。
例如:
SELECT AS VALUE 1
上面的查询会生成一个行类型为 INT64 的表。
例如:
SELECT AS VALUE STRUCT(1 AS a, 2 AS b) xyz
上面的查询会生成一个行类型为 STRUCT<a int64, b int64>
的表。
例如:
SELECT AS VALUE v FROM (SELECT AS STRUCT 1 a, true b) v WHERE v.b
若将值表 v
作为输入,上面的查询会过滤掉 WHERE
子句中的某些值,然后使用输入表中的完全相同的值生成值表。如果上面的查询未使用 SELECT AS VALUE
,则输出表架构将与输入表架构不同,因为输出表将是具有名为 v
的列且包含输入值的常规表。
别名
请参阅使用别名,了解 SELECT
列表别名的语法和可见性。
FROM 子句
from_item: { table_name [ table_hint_expr ] [ [ AS ] alias ] | join_operation | ( query_expr ) [ table_hint_expr ] [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ table_hint_expr ] [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ table_hint_expr ] [ [ AS ] alias ] }
table_hint_expr: '@{' table_hint_key = table_hint_value '}' table_hint_key: { FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION }
FROM
子句表示要从中检索行的一个或多个表,并指定如何将这些行联接在一起来生成单个行流,以便在查询的其余部分进行处理。
table_name
现有表的名称。
SELECT * FROM Roster;
表提示
表支持以下提示:
提示键 | 可能的值 | 说明 |
---|---|---|
FORCE_INDEX |
字符串。数据库中现有索引的名称,或者 _BASE_TABLE ,其表示使用基表而不是索引。 |
注意: |
GROUPBY_SCAN_OPTIMIZATION |
TRUE FALSE |
如果分组扫描优化使用 如果优化器预计优化将提高查询效率,则会应用优化。此提示会替换优化器的决定。如果此提示设置为 |
以下示例演示了在从表中读取数据时,如何通过在表名称中附加 @{FORCE_INDEX=index_name}
形式的索引指令来使用二级索引:
SELECT s.SingerId, s.FirstName, s.LastName, s.SingerInfo FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s WHERE s.FirstName = "Catalina" AND s.LastName > "M";
您可以在查询中包含多个索引,但每个不同的表引用仅支持单个索引。示例:
SELECT s.SingerId, s.FirstName, s.LastName, s.SingerInfo, c.ConcertDate FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s JOIN Concerts@{FORCE_INDEX=ConcertsBySingerId} AS c ON s.SingerId = c.SingerId WHERE s.FirstName = "Catalina" AND s.LastName > "M";
如需详细了解索引指令,请参阅二级索引。
join_operation
请参阅 JOIN 运算。
select
( select ) [ [ AS ] alias ]
是表子查询。
field_path
在 FROM
子句中,field_path
是解析为数据类型中的某字段的任意路径。field_path
可以任意深入嵌套的数据结构。
有效的 field_path
值的一些示例包括:
SELECT * FROM T1 t1, t1.array_column;
SELECT * FROM T1 t1, t1.struct_column.array_field;
SELECT (SELECT ARRAY_AGG(c) FROM t1.array_column c) FROM T1 t1;
SELECT a.struct_field1 FROM T1 t1, t1.array_of_structs a;
SELECT (SELECT STRING_AGG(a.struct_field1) FROM t1.array_of_structs a) FROM T1 t1;
FROM
子句中的字段路径必须以数组字段结尾。另外,字段路径在路径末尾之前不能包含数组。例如,路径 array_column.some_array.some_array_field
无效,因为它在路径末尾前包含数组。
UNNEST
UNNEST
运算符接受 ARRAY
,并返回一个表,ARRAY
中的每个元素占一行。您还可以在 FROM
子句外部将 UNNEST
与 IN
运算符搭配使用。
对于大多数元素类型的输入 ARRAY
,UNNEST
的输出通常有一列。此列具有一个可选 alias
,您可以使用它在查询中的其他位置引用该列。具有这些元素类型的 ARRAYS
可返回多个列:
- STRUCT
UNNEST
会破坏输入 ARRAY
中元素的顺序。请使用可选的 WITH OFFSET
子句返回另一个包含数组元素索引的列(见下文)。
如需了解多种使用 UNNEST
的方式(包括构造、展平和过滤),请参阅 Working with arrays
。
UNNEST 和 STRUCT
对于 STRUCT
的输入 ARRAY
,UNNEST
将对每个 STRUCT
返回一行,STRUCT
中的每个字段单独占一列。每列的别名是相应 STRUCT
字段的名称。
例如:
SELECT *
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')]);
+---+-----+
| x | y |
+---+-----+
| 3 | bar |
| 1 | foo |
+---+-----+
由于 UNNEST
运算符返回一个值表,因此您可以使用别名 UNNEST
定义可在查询中的其他位置引用的范围变量。如果在 SELECT
列表中引用范围变量,则查询将返回包含输入表中原始 STRUCT
的所有字段的 STRUCT
。
例如:
SELECT *, struct_value
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')])
AS struct_value;
+---+-----+--------------+
| x | y | struct_value |
+---+-----+--------------+
| 3 | bar | {3, bar} |
| 1 | foo | {1, foo} |
+---+-----+--------------+
显式和隐式 UNNEST
ARRAY
解除嵌套可以是显式的也可以是隐式的。在显式解除嵌套中,array_expression
必须返回 ARRAY
值,但不需要解析为 ARRAY
,并且 UNNEST
关键字是必需的。
例如:
SELECT * FROM UNNEST ([1, 2, 3]);
在隐式解除嵌套中,array_path
必须解析为 ARRAY
,且 UNNEST
关键字是可选的。
例如:
SELECT x
FROM mytable AS t,
t.struct_typed_column.array_typed_field1 AS x;
在这种情况下,array_path
可以任意深入数据结构中,但最后一个字段必须为 ARRAY
类型。表达式中前面的字段不能为 ARRAY
类型,因为无法从 ARRAY
中提取命名字段。
UNNEST 和 NULL
UNNEST
按如下方式处理 NULL:
- NULL 和空数组会生成零行。
- 包含 NULL 的数组会生成包含 NULL 值的行。
可选的 WITH OFFSET
子句会返回一个单独的列,其中包含 UNNEST
运算为每一行生成的“偏移量”值(即从零开始计数)。该列具有一个可选的 alias
;默认别名为 offset。
例如:
SELECT * FROM UNNEST ( ) WITH OFFSET AS num;
with_query_name
WITH
子句(请参阅 WITH 子句)中的查询名称与临时表的名称相似,您可以在 FROM
子句中的任何位置引用临时表的名称。在下面的示例中,subQ1
和 subQ2
为 with_query_names
。
例如:
WITH
subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52),
subQ2 AS (SELECT SchoolID FROM subQ1)
SELECT DISTINCT * FROM subQ2;
除非限定表名,否则 WITH
子句会在查询期间隐藏具有相同名称的任何永久表,例如:
db.Roster
。
TABLESAMPLE 运算符
tablesample_type:
TABLESAMPLE sample_method (sample_size percent_or_rows )
sample_method:
{ BERNOULLI | RESERVOIR }
sample_size:
numeric_value_expression
percent_or_rows:
{ PERCENT | ROWS }
partition_by:
PARTITION BY partition_expression [, ...]
说明
您可以使用 TABLESAMPLE
运算符选择数据集的随机样本。当处理含大量数据的表并且不需要精确答案时,该运算符非常有用。
sample_method
:使用TABLESAMPLE
运算符时,必须指定要使用的采样算法:BERNOULLI
:每一行都是按percent
子句给出的概率独立选择的。因此,您可以获得大约N * percent/100
行。RESERVOIR
:以实际样本大小 K 作为参数(以行数表示)。如果输入小于 K,则将输出整个输入关系。如果输入大于 K,则 reservoir 采样将输出一个大小为 K 的样本,其中,任何大小为 K 的样本出现的机会是相等的。
sample_size
:样本的大小。percent_or_rows
:TABLESAMPLE
运算符要求您选择ROWS
或PERCENT
。如果选择PERCENT
,则值必须介于 0 到 100 之间。如果选择ROWS
,则值必须大于或等于 0。
示例
以下示例说明了 TABLESAMPLE
运算符的用法。
使用 RESERVOIR
采样方法从表中选择:
SELECT MessageId
FROM Messages TABLESAMPLE RESERVOIR (100 ROWS);
使用 BERNOULLI
采样方法从表中选择:
SELECT MessageId
FROM Messages TABLESAMPLE BERNOULLI (0.1 PERCENT);
使用 TABLESAMPLE
和子查询:
SELECT Subject FROM
(SELECT MessageId, Subject FROM Messages WHERE ServerId="test")
TABLESAMPLE BERNOULLI(50 PERCENT)
WHERE MessageId > 3;
使用 TABLESAMPLE
运算以及与另一个表的联接。
SELECT S.Subject
FROM
(SELECT MessageId, ThreadId FROM Messages WHERE ServerId="test") AS R
TABLESAMPLE RESERVOIR(5 ROWS),
Threads AS S
WHERE S.ServerId="test" AND R.ThreadId = S.ThreadId;
别名
请参阅使用别名,了解 FROM
子句别名的语法和可见性。
JOIN 运算
join_operation: { cross_join_operation | join_operation_with_condition } cross_join_operation: from_item CROSS JOIN [ join_hint_expr ] from_item join_operation_with_condition: from_item [ join_type ] [ join_method ] JOIN [ join_hint_expr ] from_item [ { on_clause | using_clause } ] join_type: { [INNER] | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }
join_method:
{ HASH }
join_hint_expr: '@{' join_hint_key = join_hint_value [, ...] '}' join_hint_key: { FORCE_JOIN_ORDER | JOIN_METHOD } on_clause: ON bool_expression using_clause: USING ( join_column [, ...] )
JOIN
运算合并两个 from_item
,以便 SELECT
子句可以将它们作为一个源进行查询。join_type
和 ON
或 USING
子句(“联接条件”)指定如何组合和舍弃两个 from_item
中的行来形成单个源。
所有 JOIN
运算都需要 join_type
。 如果没有为 join_type
提供 JOIN
运算,则执行 INNER JOIN
。
除非以下条件中有一个成立,否则 JOIN
运算需要联接条件:
join_type
是CROSS
。- 一个或两个
from_item
不是表,例如array_path
或field_path
。
联接提示
JOIN
支持以下提示:
提示键 | 可能的值 | 说明 |
---|---|---|
FORCE_JOIN_ORDER |
TRUE FALSE (默认值) |
如果设置为 true,则使用查询中指定的联接顺序。 |
JOIN_METHOD |
HASH_JOIN APPLY_JOIN |
在实现逻辑联接时,请选择一个特定替代值用于基础联接方法。如需了解详情,请参阅联接方法。 要使用哈希联接,请使用 HASH JOIN 或 JOIN@{JOIN_METHOD=HASH_JOIN} ,但不能同时使用两者。 |
HASH_JOIN_BUILD_SIDE |
BUILD_LEFT BUILD_RIGHT |
指定将哈希联接的哪一端用作构建端。只能与 JOIN_METHOD=HASH_JOIN 搭配使用 |
BATCH_MODE |
TRUE (default) FALSE |
用于停用批量应用联接,取而代之的是逐行应用联接。只能与 JOIN_METHOD=APPLY_JOIN 搭配使用。 |
联接方法
联接方法是各种逻辑联接类型的特定实现。某些联接方法仅适用于特定联接类型。选择使用哪种联接方法取决于查询和待查询数据的具体细节。了解特定联接方法是否有助于提升查询性能的最佳方法是,尝试该方法并查看生成的查询执行计划。如需了解详情,请参阅查询执行运算符,特别是有关应用和哈希联接运算符的部分。
联接方法 | 说明 | 操作对象 |
---|---|---|
HASH_JOIN |
哈希联接运算符从一侧(构建侧)构建哈希表,并在哈希表中探测另一侧(探测侧)中的所有元素。 | 不同的变体用于各种不同的联接类型。请查看您的查询的查询执行计划,了解使用哪个变体。详细了解哈希联接运算符。 |
APPLY_JOIN |
应用联接运算符从一侧(输入侧)获取每个条目,并使用输入侧条目的值计算另一侧(映射侧)的子查询。 | 不同的变体用于各种不同的联接类型。交叉应用用于内联接,外应用用于左联接。详细了解交叉应用和外部应用运算符。 |
[INNER] JOIN
INNER JOIN
(简写为 JOIN
)有效地计算两个 from_item
的笛卡尔积,并舍弃所有不符合联接条件的行。“有效”表示可以在没有实际计算笛卡尔积的情况下实现 INNER JOIN
。
FROM A INNER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | k | | 2 | b | 2 | k |
| 2 | b | | 3 | m | | 3 | c | 3 | m |
| 3 | c | | 3 | n | | 3 | c | 3 | n |
| 3 | d | | 4 | p | | 3 | d | 3 | m |
+-------+ +-------+ | 3 | d | 3 | n |
+---------------+
FROM A INNER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +-----------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +-----------+
| 1 | a | | 2 | k | | 2 | b | k |
| 2 | b | | 3 | m | | 3 | c | m |
| 3 | c | | 3 | n | | 3 | c | n |
| 3 | d | | 4 | p | | 3 | d | m |
+-------+ +-------+ | 3 | d | n |
+-----------+
示例
此查询对 Roster
和 TeamMascot
表执行 INNER JOIN
。
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
+---------------------------+
CROSS JOIN
CROSS JOIN
返回两个 from_item
的笛卡尔积。换句话说,它将第一个 from_item
的每一行与第二个 from_item
的每一行组合。
如果两个 from_item
的行是独立的,则结果有 M * N 行,其中给定的 M 行在一个 from_item
中,而 N 行在另一个中。请注意,这适用于所有 from_item
具有零行的情况。
FROM A CROSS JOIN B
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | c | | 1 | a | 2 | c |
| 2 | b | | 3 | d | | 1 | a | 3 | d |
+-------+ +-------+ | 2 | b | 2 | c |
| 2 | b | 3 | d |
+---------------+
您可以使用相关 CROSS JOIN
来展平 ARRAY
列。在这种情况下,第二个 from_item
的行与第一个 from_item
的每一行都不同。
FROM A CROSS JOIN A.y
Table A Result
+-------------------+ +-----------+
| w | x | y | -> | w | x | y |
+-------------------+ +-----------+
| 1 | a | [P, Q] | | 1 | a | P |
| 2 | b | [R, S, T] | | 1 | a | Q |
+-------------------+ | 2 | b | R |
| 2 | b | S |
| 2 | b | T |
+-----------+
CROSS JOIN
可以显式写出,如下所示:
FROM a CROSS JOIN b
也可以隐式写出为逗号交叉联接,如下所示:
FROM a, b
逗号交叉联接不能写在括号内:
FROM a CROSS JOIN (b, c) // INVALID
请参阅 JOIN 序列,详细了解逗号交叉联接在一系列 JOIN 中的行为。
示例
此查询对 Roster
和 TeamMascot
表执行显式 CROSS JOIN
。
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Adams | Knights |
| Adams | Lakers |
| Adams | Mustangs |
| Buchanan | Jaguars |
| Buchanan | Knights |
| Buchanan | Lakers |
| Buchanan | Mustangs |
| ... |
+---------------------------+
此查询会执行逗号交叉联接,其结果与上述显式 CROSS JOIN
相同:
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster, TeamMascot;
FULL [OUTER] JOIN
FULL OUTER JOIN
(简写为 FULL JOIN
)返回两个 from_item
中满足联接条件的所有行的所有字段。
FULL
表示即使并非两个 from_item
中的所有行都符合联接条件,也全部返回。
OUTER
表示如果一个 from_item
中的给定行没有联接到其他 from_item
中的任何行,则该行将对另一个 from_item
中的所有列返回 NULL。
FROM A FULL OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 1 | a | NULL | NULL |
| 2 | b | | 3 | m | | 2 | b | 2 | k |
| 3 | c | | 3 | n | | 3 | c | 3 | m |
| 3 | d | | 4 | p | | 3 | c | 3 | n |
+-------+ +-------+ | 3 | d | 3 | m |
| 3 | d | 3 | n |
| NULL | NULL | 4 | p |
+---------------------------+
FROM A FULL OUTER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +--------------------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +--------------------+
| 1 | a | | 2 | k | | 1 | a | NULL |
| 2 | b | | 3 | m | | 2 | b | k |
| 3 | c | | 3 | n | | 3 | c | m |
| 3 | d | | 4 | p | | 3 | c | n |
+-------+ +-------+ | 3 | d | m |
| 3 | d | n |
| 4 | NULL | p |
+--------------------+
示例
此查询对 Roster
和 TeamMascot
表执行 FULL JOIN
。
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster FULL JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
| Eisenhower | NULL |
| NULL | Mustangs |
+---------------------------+
LEFT [OUTER] JOIN
两个 from_item
的 LEFT OUTER JOIN
(简写为 LEFT JOIN
)结果将始终保留 JOIN
运算中左侧 from_item
的所有行,即使右侧 from_item
没有满足联接谓词的行也是如此。
LEFT
表示返回左侧 from_item
中的所有行;如果左侧 from_item
中的给定行没有联接到右侧 from_item
中的任何行,则该行将对右侧 from_item
中的所有列返回 NULL。右侧 from_item
中没有联接到左侧 from_item
的任何行都将被舍弃。
FROM A LEFT OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 1 | a | NULL | NULL |
| 2 | b | | 3 | m | | 2 | b | 2 | k |
| 3 | c | | 3 | n | | 3 | c | 3 | m |
| 3 | d | | 4 | p | | 3 | c | 3 | n |
+-------+ +-------+ | 3 | d | 3 | m |
| 3 | d | 3 | n |
+---------------------------+
FROM A LEFT OUTER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +--------------------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +--------------------+
| 1 | a | | 2 | k | | 1 | a | NULL |
| 2 | b | | 3 | m | | 2 | b | k |
| 3 | c | | 3 | n | | 3 | c | m |
| 3 | d | | 4 | p | | 3 | c | n |
+-------+ +-------+ | 3 | d | m |
| 3 | d | n |
+--------------------+
示例
此查询对 Roster
和 TeamMascot
表执行 LEFT JOIN
。
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster LEFT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
| Eisenhower | NULL |
+---------------------------+
RIGHT [OUTER] JOIN
RIGHT OUTER JOIN
(简写为 RIGHT JOIN
)的结果与 LEFT OUTER JOIN
类似,互为对称。
FROM A RIGHT OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 2 | b | 2 | k |
| 2 | b | | 3 | m | | 3 | c | 3 | m |
| 3 | c | | 3 | n | | 3 | c | 3 | n |
| 3 | d | | 4 | p | | 3 | d | 3 | m |
+-------+ +-------+ | 3 | d | 3 | n |
| NULL | NULL | 4 | p |
+---------------------------+
FROM A RIGHT OUTER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +--------------------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +--------------------+
| 1 | a | | 2 | k | | 2 | b | k |
| 2 | b | | 3 | m | | 3 | c | m |
| 3 | c | | 3 | n | | 3 | c | n |
| 3 | d | | 4 | p | | 3 | d | m |
+-------+ +-------+ | 3 | d | n |
| 4 | NULL | p |
+--------------------+
示例
此查询对 Roster
和 TeamMascot
表执行 RIGHT JOIN
。
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster RIGHT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
| NULL | Mustangs |
+---------------------------+
ON 子句
ON
子句包含 bool_expression
。如果 bool_expression
返回 TRUE,则组合的行(联接两行的结果)符合联接条件。
FROM A JOIN B ON A.x = B.x
Table A Table B Result (A.x, B.x)
+---+ +---+ +-------+
| x | * | x | = | x | x |
+---+ +---+ +-------+
| 1 | | 2 | | 2 | 2 |
| 2 | | 3 | | 3 | 3 |
| 3 | | 4 | +-------+
+---+ +---+
示例
此查询对 Roster
和 TeamMascot
表执行 INNER JOIN
。
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
+---------------------------+
USING 子句
USING
子句需要一个 column_list
,其中包含出现在这两个输入表中的一个或多个列。该子句对这些列执行相等比较,如果相等比较返回 TRUE,则行满足联接条件。
FROM A JOIN B USING (x)
Table A Table B Result
+---+ +---+ +---+
| x | * | x | = | x |
+---+ +---+ +---+
| 1 | | 2 | | 2 |
| 2 | | 3 | | 3 |
| 3 | | 4 | +---+
+---+ +---+
示例
此查询对 Roster
和 TeamMascot
表执行 INNER JOIN
。
此语句返回 Roster
和 TeamMascot
中 Roster.SchooldID
与 TeamMascot.SchooldID
相同的行。结果包括一个 SchooldID
列。
SELECT * FROM Roster INNER JOIN TeamMascot USING (SchoolID);
+----------------------------------------+
| SchoolID | LastName | Mascot |
+----------------------------------------+
| 50 | Adams | Jaguars |
| 52 | Buchanan | Lakers |
| 52 | Coolidge | Lakers |
| 51 | Davis | Knights |
+----------------------------------------+
ON 和 USING 的等效情况
ON
和 USING
关键字并不等效,但它们是类似的。ON
返回多列,USING
返回一列。
FROM A JOIN B ON A.x = B.x
FROM A JOIN B USING (x)
Table A Table B Result ON Result USING
+---+ +---+ +-------+ +---+
| x | * | x | = | x | x | | x |
+---+ +---+ +-------+ +---+
| 1 | | 2 | | 2 | 2 | | 2 |
| 2 | | 3 | | 3 | 3 | | 3 |
| 3 | | 4 | +-------+ +---+
+---+ +---+
虽然 ON
和 USING
不等效,但如果您指定要返回的列,它们可以返回相同的结果。
SELECT x FROM A JOIN B USING (x);
SELECT A.x FROM A JOIN B ON A.x = B.x;
Table A Table B Result
+---+ +---+ +---+
| x | * | x | = | x |
+---+ +---+ +---+
| 1 | | 2 | | 2 |
| 2 | | 3 | | 3 |
| 3 | | 4 | +---+
+---+ +---+
JOIN 序列
FROM
子句可以在一个序列中包含多个 JOIN
运算。JOIN
按从左到右的顺序绑定。例如:
FROM A JOIN B USING (x) JOIN C USING (x)
-- A JOIN B USING (x) = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 = return value
您还可以插入括号来对 JOIN
分组:
FROM ( (A JOIN B USING (x)) JOIN C USING (x) )
-- A JOIN B USING (x) = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 = return value
通过括号,您可以将 JOIN
分组,从而使它们按不同的顺序绑定:
FROM ( A JOIN (B JOIN C USING (x)) USING (x) )
-- B JOIN C USING (x) = result_1
-- A JOIN result_1 = result_2
-- result_2 = return value
当逗号交叉联接出现在具有一系列 JOIN 的查询中时,它们会像其他 JOIN
类型一样按从左到右的顺序进行分组:
FROM A JOIN B USING (x) JOIN C USING (x), D
-- A JOIN B USING (x) = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 CROSS JOIN D = return value
逗号联接后不能有 RIGHT JOIN
或 FULL JOIN
:
FROM A, B RIGHT JOIN C ON TRUE // INVALID
FROM A, B FULL JOIN C ON TRUE // INVALID
FROM A, B JOIN C ON TRUE // VALID
WHERE 子句
WHERE bool_expression
WHERE
子句根据 bool_expression
计算每一行,以此来过滤行,并舍弃所有未返回 TRUE 的行(即返回 FALSE 或 NULL 的行)。
例如:
SELECT * FROM Roster
WHERE SchoolID = 52;
bool_expression
可以包含多个子条件。
例如:
SELECT * FROM Roster
WHERE STARTS_WITH(LastName, "Mc") OR STARTS_WITH(LastName, "Mac");
不能在 WHERE
子句中引用 SELECT
列表的列别名。
INNER JOIN
中的表达式具有 WHERE
子句中的等效表达式。例如,使用 INNER
JOIN
和 ON
的查询具有使用 CROSS JOIN
和 WHERE
的等效表达式。
示例 - 此查询:
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
等效于:
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;
GROUP BY 子句
GROUP BY expression [, ...]
GROUP BY
子句将表中在 GROUP BY
子句具有相同 expression
值的行合并。对于源表中具有相同 expression
值的多个行,GROUP BY
子句会生成一个组合行。当 SELECT
列表中存在聚合函数或要消除输出中的冗余时,通常使用 GROUP BY
。expression
的数据类型必须是可分组的。
例如:
SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName;
GROUP BY
子句可以引用 SELECT
列表中的表达式名称。GROUP BY
子句还允许使用整数值对 SELECT
列表中的表达式进行序号引用。1
是指在 SELECT
列表中的第一个表达式,2
是指第二个表达式,以此类推。表达式列表可以组合序号和表达式名称。
例如:
SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY LastName, FirstName;
上面的查询等效于:
SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY 2, FirstName;
GROUP BY
子句也可能引用别名。如果查询包含 SELECT
子句中的别名,则这些别名会替换相应 FROM
子句中的名称。
示例:
SELECT SUM(PointsScored), LastName as last_name
FROM PlayerStats
GROUP BY last_name;
HAVING 子句
HAVING bool_expression
HAVING
子句与 WHERE
子句类似:它会过滤掉在根据 bool_expression
进行计算后未返回 TRUE 的行。
与 WHERE
子句一样,bool_expression
可以是任何返回布尔值的表达式,并且可以包含多个子条件。
HAVING
子句与 WHERE
子句的不同之处在于:
HAVING
子句要求查询中存在GROUP BY
或聚合。HAVING
子句出现在GROUP BY
和聚合之后、ORDER BY
之前。这意味着结果集中的每个聚合行都会计算HAVING
子句一次。这与WHERE
子句不同,该子句在GROUP BY
和聚合之前计算。
HAVING
子句可以引用通过 FROM
子句提供的列以及 SELECT
列表别名。HAVING
子句中引用的表达式必须出现在 GROUP BY
子句中,或者它们必须是聚合函数的结果:
SELECT LastName
FROM Roster
GROUP BY LastName
HAVING SUM(PointsScored) > 15;
如果查询包含 SELECT
子句中的别名,则这些别名会替换 FROM
子句中的名称。
SELECT LastName, SUM(PointsScored) AS ps
FROM Roster
GROUP BY LastName
HAVING ps > 0;
强制聚合
聚合不必存在于 HAVING
子句本身中,但聚合必须至少以下列一种形式存在:
SELECT
列表中的聚合函数。
SELECT LastName, SUM(PointsScored) AS total
FROM PlayerStats
GROUP BY LastName
HAVING total > 15;
HAVING 子句中的聚合函数。
SELECT LastName
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;
SELECT
列表和 HAVING
子句中的聚合。
当聚合函数同时存在于 SELECT
列表和 HAVING
子句中时,聚合函数及其引用的列不需要相同。在下面的示例中,COUNT()
和 SUM()
这两个聚合函数是不同的,也使用不同的列。
SELECT LastName, COUNT(*)
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;
ORDER BY 子句
ORDER BY expression [{ ASC | DESC }] [, ...]
ORDER BY
子句将某个列或表达式指定为结果集的排序标准。如果 ORDER BY 子句不存在,则查询结果的顺序未定义。允许 FROM
子句或 SELECT
列表中的列别名。如果查询包含 SELECT
子句中的别名,则这些别名会替换相应 FROM
子句中的名称。
可选子句
ASC | DESC
:按expression
值的升序或降序顺序对结果进行排序。ASC
为默认值。
示例
使用默认排序顺序(升序)。
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true)
ORDER BY x;
+------+-------+
| x | y |
+------+-------+
| 1 | true |
| 9 | true |
+------+-------+
使用降序排序顺序。
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true)
ORDER BY x DESC;
+------+-------+
| x | y |
+------+-------+
| 9 | true |
| 1 | true |
+------+-------+
可以按多列进行排序。在下面的示例中,结果集首先按 SchoolID
排序,然后按 LastName
排序:
SELECT LastName, PointsScored, OpponentID
FROM PlayerStats
ORDER BY SchoolID, LastName;
对值进行排序时,以下规则适用:
- NULL:在
ORDER BY
子句的上下文中,NULL 是可能的最小值;也就是说,NULL 在ASC
排序方向中出现在最前位置,在DESC
排序方向中出现在最后位置。 - 浮点数据类型:请参阅浮点语义,了解排序和分组。
将 ORDER BY
子句与集合运算符一起使用时,该子句将应用于整个查询的结果集,而不仅仅应用于最近的 SELECT
语句。因此,使用括号显示 ORDER
BY
的范围会很有帮助(但不是必需的)。
以下不带括号的查询:
SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot
ORDER BY SchoolID;
等效于以下带括号的查询:
( SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot )
ORDER BY SchoolID;
但不等效于以下查询,其中 ORDER BY
子句仅应用于第二个 SELECT
语句:
SELECT * FROM Roster
UNION ALL
( SELECT * FROM TeamMascot
ORDER BY SchoolID );
您还可以在 ORDER BY
子句中使用整数字面量作为列引用。整数字面量成为 SELECT
列表中的序号(例如,从 1 开始计数)。
示例 - 以下两个查询是等效的:
SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY LastName;
SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY 2;
COLLATE
您可以使用 COLLATE
子句细化 ORDER
BY
子句中数据的排序方式。排序规则是一组用于确定如何根据特定语言、区域或国家/地区的惯例和标准对字符串进行比较的规则。这些规则可以定义正确的字符序列,并且提供用于指定不区分大小写的选项。
您可以向语句添加排序规则,如下所示:
SELECT ...
FROM ...
ORDER BY value COLLATE collation_string
collation_string
包含 collation_name
,并且可以使用可选的 collation_attribute
作为后缀(以英文冒号分隔)。collation_string
是字面量或参数。通常,此名称由表示语言的两个字母组成,后面可能会跟下划线和表示区域的两个字母(例如 en_US
)。这些名称由通用语言区域数据代码库 (CLDR)定义。
语句也可以包含 unicode
这一 collation_name
。此值表示语句应使用默认的 unicode 排序规则返回数据。
除了 collation_name
,collation_string
还可以使用可选的 collation_attribute
作为后缀(以英文冒号分隔)。此特性用于指定数据比较是否应区分大小写。如果区分大小写,则允许的值为 cs
;如果不区分大小写,则允许的值为 ci
。如果未提供 collation_attribute
,则使用 CLDR 默认值。
示例
使用“英文 - 加拿大”对结果进行排序:
SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_CA"
使用参数对结果进行排序:
#@collate_param = "arg_EG"
SELECT Place
FROM Locations
ORDER BY Place COLLATE @collate_param
在一个语句中使用多个 COLLATE
子句:
SELECT APlace, BPlace, CPlace
FROM Locations
ORDER BY APlace COLLATE "en_US" ASC,
BPlace COLLATE "ar_EG" DESC,
CPlace COLLATE "en" DESC
不区分大小写的排序规则:
SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_US:ci"
默认 Unicode 不区分大小写的排序规则:
SELECT Place
FROM Locations
ORDER BY Place COLLATE "unicode:ci"
集合运算符
UNION { ALL | DISTINCT } | INTERSECT { ALL | DISTINCT } | EXCEPT { ALL | DISTINCT }
集合运算符将两个或更多输入查询的结果合并到一个结果集中。必须指定 ALL
或 DISTINCT
;如果指定 ALL
,将保留所有行。如果指定 DISTINCT
,将舍弃重复的行。
如果给定行 R 在第一个输入查询中正好出现 m 次,在第二个输入查询中出现 n 次(m >= 0,n >= 0):
- 对于
UNION ALL
,R 将在结果中正好出现 m + n 次。 - 对于
INTERSECT ALL
,R 将在结果中正好出现MIN(m, n)
。 - 对于
EXCEPT ALL
,R 将在结果中正好出现MAX(m - n, 0)
。 - 对于
UNION DISTINCT
,先计算UNION
,再计算DISTINCT
,因此 R 恰好出现一次。 - 对于
INTERSECT DISTINCT
,先计算上述结果,再计算DISTINCT
。 - 对于
EXCEPT DISTINCT
,如果 m > 0 且 n = 0,则行 R 在输出中出现一次。 - 如果输入查询超过两个,则上述运算会进行泛化,并且输出结果与输入从左到右递增组合的情形相同。
以下规则适用:
- 对于
UNION ALL
以外的集合运算,所有列类型都必须支持相等比较。 - 运算符两侧的输入查询都必须返回相同数量的列。
- 运算符根据列在各自
SELECT
列表中的位置对每个输入查询返回的列进行配对。也就是说,第一个输入查询中的第一列与第二个输入查询中的第一列配对。 - 结果集始终使用第一个输入查询中的列名称。
- 结果集始终在相应列中使用输入类型的超类型,因此配对列也必须具有相同的数据类型或通用超类型。
- 必须使用括号分隔不同的集合运算;因此,集合运算(如
UNION ALL
和UNION DISTINCT
)是不同的。如果语句只重复相同的集合运算,则不需要括号。
示例:
query1 UNION ALL (query2 UNION DISTINCT query3)
query1 UNION ALL query2 UNION ALL query3
无效:
query1 UNION ALL query2 UNION DISTINCT query3
query1 UNION ALL query2 INTERSECT ALL query3; // INVALID.
UNION
UNION
运算符将每个查询的结果集中的列进行配对并以垂直方式连接这些列,以此来合并两个或更多输入查询的结果集。
INTERSECT
INTERSECT
运算符返回在左侧和右侧输入查询的结果集中都能找到的行。与 EXCEPT
不同,输入查询的定位(INTERSECT
运算符的左侧和右侧)无关紧要。
EXCEPT
EXCEPT
运算符返回左侧输入查询中不存在于右侧输入查询中的行。
例如:
SELECT * FROM UNNEST(ARRAY<int64>[1, 2, 3]) AS number
EXCEPT DISTINCT SELECT 1;
+--------+
| number |
+--------+
| 2 |
| 3 |
+--------+
LIMIT 和 OFFSET 子句
LIMIT count [ OFFSET skip_rows ]
LIMIT
指定 INT64 类型的非负 count
,返回的行数不会超过 count
。LIMIT
0
返回 0 行。
如果存在集合运算,则将在集合运算求值后应用 LIMIT
。
OFFSET
指定在应用 LIMIT
之前要跳过的非负行数。skip_rows
的类型为 INT64。
这些子句只接受字面量或参数值。除非在 ORDER BY
之后使用这些运算符,否则 LIMIT
和 OFFSET
返回的行并不明确。
示例:
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 2
+---------+
| letter |
+---------+
| a |
| b |
+---------+
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 3 OFFSET 1
+---------+
| letter |
+---------+
| b |
| c |
| d |
+---------+
WITH 子句
WITH
子句会将一个或多个已命名的子查询的结果绑定到临时表名称。每个引入的表名称在同一查询表达式内的后续 SELECT
表达式中都是可见的。这包括以下几种 SELECT
表达式:
- 后续
WITH
绑定中的任何SELECT
表达式 - 查询表达式中位于集合运算符(例如
UNION
)两侧的顶级SELECT
表达式 - 同一查询表达式内的子查询中的
SELECT
表达式
示例:
WITH subQ1 AS (SELECT SchoolID FROM Roster),
subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2;
子查询不支持 WITH
。
将返回一个错误:
SELECT account
FROM (
WITH result AS (SELECT * FROM NPCs)
SELECT *
FROM result);
不支持 WITH RECURSIVE
。
DML 语句不支持 WITH
子句。
由 WITH
子句定义的临时表会存储在内存中。Cloud Spanner SQL 可为查询创建的所有临时表动态分配内存。如果可用资源不足,则查询将失败。
使用别名
别名是为查询中存在的表、列或表达式提供的临时名称。您可以在 SELECT
列表或 FROM
子句中引入显式别名,否则 Cloud Spanner SQL 将推断某些表达式的隐式别名。
既没有显式别名也没有隐式别名的表达式是匿名的,查询无法通过名称引用它们。
显式别名
您可以在 FROM
子句或 SELECT
列表中引入显式别名。
在 FROM
子句中,可以使用 [AS] alias
为任何项(包括表、数组、子查询和 UNNEST
子句)引入显式别名。AS
关键字是可选的。
例如:
SELECT s.FirstName, s2.SongName
FROM Singers AS s, (SELECT * FROM Songs) AS s2;
可以使用 [AS] alias
为 SELECT
列表中的任何表达式引入显式别名。AS
关键字是可选的。
例如:
SELECT s.FirstName AS name, LOWER(s.FirstName) AS lname
FROM Singers s;
隐式别名
在 SELECT
列表中,如果有一个表达式没有显式别名,Cloud Spanner SQL 将根据以下规则分配隐式别名。在 SELECT
列表中可以有多个具有相同别名的列。
- 对于标识符,别名就是标识符。例如,
SELECT abc
表示AS abc
。 - 对于路径表达式,别名是路径中的最后一个标识符。例如,
SELECT abc.def.ghi
表示AS ghi
。 - 对于使用“点”成员字段访问运算符的字段访问,别名是字段名称。例如,
SELECT (struct_function()).fname
表示AS fname
。
在所有其他情况下,不存在隐式别名,因此列是匿名的,不能通过名称引用。系统仍将返回该列的数据,显示的查询结果可能会有为该列生成的标签,但标签不能像别名一样使用。
在 FROM
子句中,from_item
不需要具有别名。以下规则适用:
- 如果有一个表达式没有显式别名,在以下情况下,Cloud Spanner SQL 会分配隐式别名:
-
对于标识符,别名就是标识符。例如,
FROM abc
表示AS abc
。 -
对于路径表达式,别名是路径中的最后一个标识符。例如,
FROM abc.def.ghi
表示AS ghi
。 - 使用
WITH OFFSET
生成的列具有隐式别名offset
。 - 表子查询不具有隐式别名。
FROM UNNEST(x)
不具有隐式别名。
别名可见性
在查询中引入显式别名后,在查询的其他位置引用该别名存在限制。这些别名可见性限制是 Cloud Spanner SQL 的名称范围限定规则造成的结果。
FROM 子句中的可见性
Cloud Spanner SQL 从左到右处理 FROM
子句中的别名,并且别名仅对 FROM
子句中的后续路径表达式可见。
示例:
假设 Singers
表有 ARRAY
类型的 Concerts
列。
SELECT FirstName
FROM Singers AS s, s.Concerts;
无效:
SELECT FirstName
FROM s.Concerts, Singers AS s; // INVALID.
FROM
子句别名对同一 FROM
子句中的子查询不可见。FROM
子句中的子查询不能在同一 FROM
子句中包含对其他表的相关引用。
无效:
SELECT FirstName
FROM Singers AS s, (SELECT (2020 - ReleaseDate) FROM s) // INVALID.
您可以在查询中的任何位置使用 FROM
中某个表的任何列名作为别名,无论是否具有表名称的资格。
例如:
SELECT FirstName, s.ReleaseDate
FROM Singers s WHERE ReleaseDate = 1975;
如果 FROM
子句包含显式别名,则必须在查询的其余部分使用显式别名而不是隐式别名(请参阅隐式别名)。如果出现自联接等情况,在查询处理期间多次扫描同一个表时,表别名可用于简化表达或消除歧义。
例如:
SELECT * FROM Singers as s, Songs as s2
ORDER BY s.LastName
无效 - ORDER BY
不使用表别名:
SELECT * FROM Singers as s, Songs as s2
ORDER BY Singers.LastName; // INVALID.
SELECT 列表中的可见性
SELECT
列表中的别名仅对以下子句可见:
GROUP BY
子句ORDER BY
子句HAVING
子句
例如:
SELECT LastName AS last, SingerID
FROM Singers
ORDER BY last;
GROUP BY、ORDER BY、HAVING 子句中的可见性
GROUP BY
、ORDER BY
和 HAVING
三个子句只能引用以下值:
FROM
子句中的表及其任何列。SELECT
列表中的别名。
GROUP BY
和 ORDER BY
还可以引用第三个组:
- 整型字面量,其引用
SELECT
列表中的项。整数1
表示SELECT
列表中的第一项,2
表示第二项等。
例如:
SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY 1
ORDER BY 2 DESC;
上述查询等效于以下查询:
SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY sid
ORDER BY s2id DESC;
别名重复
系统允许使用包含同一名称的多个显式或隐式别名的 SELECT
列表或子查询,只要查询中的其他位置没有引用该别名即可(因为引用可能会有歧义)。
例如:
SELECT 1 AS a, 2 AS a;
+---+---+
| a | a |
+---+---+
| 1 | 2 |
+---+---+
有歧义的别名
如果访问名称有歧义,则 Cloud Spanner SQL 会报错,这意味着该名称可以解析为查询或表架构(包括目标表的架构)中的不止一个唯一对象。
示例:
此查询包含表之间冲突的列名称,因为 Singers
和 Songs
都有一个名为 SingerID
的列:
SELECT SingerID
FROM Singers, Songs;
此查询包含在 GROUP BY
子句中有歧义的别名,因为它们在 SELECT
列表中重复:
SELECT FirstName AS name, LastName AS name,
FROM Singers
GROUP BY name;
此查询包含在 SELECT
列表和 FROM
子句中有歧义的别名,因为它们的名称相同:假设 table
包含 x
列、y
列和 z
列。z
是 STRUCT 类型,具有字段 v
、w
、x
。
例如:
SELECT x, z AS T
FROM table AS T
GROUP BY T.x;
别名 T
有歧义,会产生错误,因为 GROUP
BY
子句中的 T.x
可能引用 table.x
或 table.z.x
。
如果某名称既是列名又是 SELECT
列表别名,那么只要该名称解析为相同的基础对象,就不会在 GROUP BY
、ORDER BY
或 HAVING
中产生歧义。
例如:
SELECT LastName, BirthYear AS BirthYear
FROM Singers
GROUP BY BirthYear;
别名 BirthYear
不会有歧义,因为它解析为相同的基础列 Singers.BirthYear
。
隐式别名
在 SELECT
列表中,如果有一个表达式没有显式别名,Cloud Spanner SQL 将根据以下规则分配隐式别名。在 SELECT
列表中可以有多个具有相同别名的列。
- 对于标识符,别名就是标识符。例如,
SELECT abc
表示AS abc
。 - 对于路径表达式,别名是路径中的最后一个标识符。例如,
SELECT abc.def.ghi
表示AS ghi
。 - 对于使用“点”成员字段访问运算符的字段访问,别名是字段名称。例如,
SELECT (struct_function()).fname
表示AS fname
。
在所有其他情况下,不存在隐式别名,因此列是匿名的,不能通过名称引用。系统仍将返回该列的数据,显示的查询结果可能会有为该列生成的标签,但标签不能像别名一样使用。
在 FROM
子句中,from_item
不需要具有别名。以下规则适用:
- 如果有一个表达式没有显式别名,在以下情况下,Cloud Spanner SQL 会分配隐式别名:
- 对于标识符,别名就是标识符。例如,
FROM abc
表示AS abc
。 - 对于路径表达式,别名是路径中的最后一个标识符。例如,
FROM abc.def.ghi
表示AS ghi
。 - 使用
WITH OFFSET
生成的列具有隐式别名offset
。
- 对于标识符,别名就是标识符。例如,
- 表子查询不具有隐式别名。
FROM UNNEST(x)
不具有隐式别名。
范围变量
在 Cloud Spanner SQL 中,范围变量是 FROM
子句中的表表达式别名。有时,范围变量称为 table alias
。范围变量允许您引用正在从表表达式中扫描的行。表表达式表示 FROM
子句中返回表的项。此表达式可以表示的常用项包括表、值表、子查询、联接和带圆括号的联接。
通常,范围变量提供对表表达式行的引用。范围变量可用于限定列引用并明确标识相关表,例如 range_variable.column_1
。
在没有指定列后缀的情况下直接引用范围变量时,表表达式的结果是相关表的行类型。值表具有显式行类型,因此对于与值表相关的范围变量,结果类型是值表的行类型。其他表没有显式行类型,对于这些表,范围变量类型是一个动态定义的 STRUCT
,其中包含表中的所有列。
示例
在这些示例中,WITH
子句用于模拟名为 Grid
的临时表。此表包含 x
和 y
列。名为 Coordinate
的范围变量指的是系统扫描表时的当前行。Coordinate
可用于访问该行中的整行或整列。
以下示例从范围变量 Coordinate
中选择列 x
,这实际上从表 Grid
中选择列 x
。
WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.x FROM Grid AS Coordinate;
+---+
| x |
+---+
| 1 |
+---+
以下示例从范围变量 Coordinate
中选择所有列,这实际上从表 Grid
中选择所有列。
WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.* FROM Grid AS Coordinate;
+---+---+
| x | y |
+---+---+
| 1 | 2 |
+---+---+
以下示例选择范围变量 Coordinate
,它是对表 Grid
中的行的引用。由于 Grid
不是值表,因此 Coordinate
的结果类型为 STRUCT
,其中包含 Grid
中的所有列。
WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate FROM Grid AS Coordinate;
+--------------+
| Coordinate |
+--------------+
| {x: 1, y: 2} |
+--------------+
附录 A:样本数据示例
这些示例包括对 Roster
、TeamMascot
、PlayerStats
表执行查询的语句。
GROUP BY 子句
例如:
SELECT LastName, SUM(PointsScored)
FROM PlayerStats
GROUP BY LastName;
LastName | SUM |
---|---|
Adams | 7 |
Buchanan | 13 |
Coolidge | 1 |
UNION
UNION
运算符将每个 SELECT
语句的结果集中的列进行配对并以垂直方式连接这些列,以此来合并两个或更多 SELECT
语句的结果集。
例如:
SELECT Mascot AS X, SchoolID AS Y
FROM TeamMascot
UNION ALL
SELECT LastName, PointsScored
FROM PlayerStats;
结果:
X | Y |
---|---|
Jaguars | 50 |
Knights | 51 |
Lakers | 52 |
Mustangs | 53 |
Adams | 3 |
Buchanan | 0 |
Coolidge | 1 |
Adams | 4 |
Buchanan | 13 |
INTERSECT
此查询将返回 Roster 和 PlayerStats 中存在的姓氏。
SELECT LastName
FROM Roster
INTERSECT ALL
SELECT LastName
FROM PlayerStats;
结果:
LastName |
---|
Adams |
Coolidge |
Buchanan |
EXCEPT
下面的查询返回 Roster 中存在而 PlayerStats 中不存在的姓氏。
SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;
结果:
LastName |
---|
Eisenhower |
Davis |
颠倒 SELECT
语句的顺序将返回 PlayerStats 中存在而 Roster 中不存在的姓氏:
SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;
结果:
(empty)