FROM 子句

📄字数 7.7K

👁️阅读量加载中...

指定数据来源表，可将堆表、子查询、表连接结果集、XMLTable等方式作为数据来源。

一、主要语法结构

1.1 语法格式

(select_with_parens::=, alias_clause::=, opt_pivot_unpivot_clause::=, joined_table::=)

1.2 参数说明

dblink_name：指定DBLink名称
table_name：指定表名称
xmltable_exprs：指定XML表表达式，请参阅XML表子句

二、别名子句

2.1 语法格式

2.2 参数说明

ColumnName：指定表别名为ColumnName
( name_list )：
- 未指定：不为表的列设置别名
- name_list：指定列别名列表，使用,逗号分隔，此列表从表的首列开始，按序逐一设置列别名

三、连接子句

3.1 语法格式

3.2 参数说明

圆括号形式语句：

( joined_table )：支持连接子句圆括号包裹形式

交叉连接语句：

交叉连接即笛卡尔积，无连接条件，数据库将返回一个表的每一行与另一个表的每一行相结合的结果行，若连接表数据量过多则应避免使用交叉连接产生过多数据。

table_ref CROSS JOIN table_ref：
- table_ref（1）：交叉连接的左支表子句，请参阅主要语法结构
- table_ref（2）：交叉连接的右支表子句，请参阅主要语法结构

自然连接语句：

特殊的等值连接，不可指定连接条件，数据库根据关系表中的相同字段进行条件连接，使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列，若无相同字段则返回连接表笛卡尔积。

table_ref NATURAL join_type JOIN table_ref：
- table_ref（1）：自然连接的左支表子句，请参阅主要语法结构
- join_type：连接类型
  - 未指定：内连接，等同于 INNER
  - FULL OUTER：全外连接
  - LEFT OUTER：左外连接
  - RIGHT OUTER：右外连接
  - INNER：内连接
- table_ref（2）：自然连接的右支表子句，请参阅主要语法结构

联合连接语句：

无连接条件，不执行数据匹配，仅将两表简单联合，对一张表数据每一行，另一张表字段部分全为NULL值。

table_ref UNIONJOIN table_ref：
- table_ref（1）：联合连接的左支表子句，请参阅主要语法结构
- table_ref（2）：联合连接的右支表子句，请参阅主要语法结构

非自然连接语句，指定列名列表作为连接条件：

table_ref join_type JOIN table_ref USING ( name_list ) opt_pivot_unpivot_clause：
- table_ref（1）：非自然连接的左支表子句，请参阅主要语法结构
- join_type：连接类型
  - 未指定：内连接，等同于 INNER
  - FULL OUTER：全外连接
  - LEFT OUTER：左外连接
  - RIGHT OUTER：右外连接
  - INNER：内连接
- table_ref（2）：非自然连接的右支表子句，请参阅主要语法结构
- name_list：指定连接列名列表，使用,逗号分隔，列名必须在两表中均存在
- opt_pivot_unpivot_clause：指定PIVOT子句，请参阅PIVOT/UNPIVOT子句

非自然连接语句，指定布尔表达式作为连接条件：

table_ref join_type JOIN table_ref ON bool_expr opt_pivot_unpivot_clause：
- table_ref（1）：非自然连接的左支表子句，请参阅主要语法结构
- join_type：连接类型
  - 未指定：内连接，等同于 INNER
  - FULL OUTER：全外连接
  - LEFT OUTER：左外连接
  - RIGHT OUTER：右外连接
  - INNER：内连接
- table_ref（2）：非自然连接的右支表子句，请参阅主要语法结构
- bool_expr：指定连接条件布尔表达式，请参阅逻辑表达式
- opt_pivot_unpivot_clause：指定PIVOT子句，请参阅PIVOT/UNPIVOT子句

四、XML表子句

4.1 语法格式

4.2 参数说明

xmltable_exprs：
- c_expr（1）：XQuery语句，请参阅XQuery
- c_expr（2）：源表XML数据列，格式为字段名或表名.字段名
- xmltable_new_columns：构造的XMLTable字段列表，使用,逗号分隔
  - xmltable_new_column：
    - name：XMLTable字段名
    - STypename1：XMLTable字段类型名
    - c_expr：XML语句中的特定标签名
alias_clause：别名子句，请参阅别名子句

提示

更多有关XMLTable的详细信息请参阅XMLTable。

五、PIVOT/UNPIVOT子句

将一组数据使用 PIVOT 子句从行旋转为列，或使用 UNPIVOT 子句从列旋转为行。PIVOT 操作在旋转过程中聚合数据，新的列表示不同范围的聚合数据，PIVOT 操作的输出通常比初始数据行包含更多的列和更少的行。UNPIVOT 是 PIVOT 的相反操作，它将一组数据从列旋转为行。

5.1 语法格式

5.2 参数说明

opt_pivot_unpivot_clause：

( pivot_clause | unpivot_clause )：
- 未指定：不使用 PIVOT/UNPIVOT 子句
- pivot_clause：使用 PIVOT 行转列子句
- unpivot_clause：使用 UNPIVOT 列转行子句

pivot_clause（PIVOT 行转列子句）：

数据库会为每个新列生成一个名称。如果未指定聚合函数的别名，那么数据库将使用每个旋转列的值作为每个聚合值被转置到的新列的名称。如果为聚合函数指定了别名，那么数据库将按照旋转列的名称_聚合函数别名的格式连接，为每个聚合值被转置到的新列生成一个名称。如果生成的列名超过了列名的最大长度，那么会返回[E19121] 标识符长度超过最大长度128错误。为了避免这个问题，请为旋转列标题、聚合函数或两者指定一个较短的别名。

PIVOT XML ( aggr_func_list pivot_for_clause pivot_in_clause ) alias_clause：
- XML：可选XML关键字，指定数据返回XML格式，功能暂未支持，若指定将报错
- aggr_func_list：指定对表或视图中的字段使用的聚集函数列表，以,逗号分隔
  注意
  - 其返回结果和数据库的设计有关，请以实际为准
  - 使用多个聚集函数时，只允许其中一个不指定别名，其余聚集函数必须指定别名
  - aggr_func：聚合函数，请参阅聚合函数
    - c_expr：需要聚合的数据表达式
    - opt_as_alias：
      - 未指定：不设置聚合函数别名
      - AS ColLabel：指定聚合函数别名
      - ColumnName：指定聚合函数别名
- pivot_for_clause：指定表或视图中的原始字段，或字段列表
  - column_list：指定需要做转换的原始字段名列表，以,逗号分隔
    - ColLabel：字段名称
- pivot_in_clause：对 pivot_for_clause 指定列表中的数据进行筛选，指定的数据值或别名将作为旋转后新列的列名，如果没有提供别名，列名将变为带引号的标识符。expr 直接指定 pivot_for_clause 列表中列的数据值，一般使用常量表达式
  - IN ( wildcard_list )：指定原始列名通配符列表，使用,逗号分隔，功能暂未支持，指定将报错
    - ANY：匹配任意值
  - IN ( select_no_parens )：指定查询结果表作为列名，请参阅SELECT，功能暂未支持，指定将报错
  - IN ( pivot_in_expr_list )：指定原始列名列表，使用,逗号分隔
    - expr_opt_as：
      - const_expr_item：
        b_expr：字段表达式
        ( expr_list_more )：字段表达式列表，使用,逗号分隔
      - opt_as_alias：
        未指定：不设置字段别名，使用原字段名，返回查询结果中，列名将被单引号包裹
        AS ColLabel：指定字段别名
        ColumnName：指定字段别名
- alias_clause：指定表的别名，请参阅别名子句

unpivot_clause（UNPIVOT 列转行子句）：

UNPIVOT ( ( INCLUDE | EXCLUDE ) NULLS ) ( column_list pivot_for_clause unpivot_in_clause ) alias_clause
- ( ( INCLUDE | EXCLUDE ) NULLS )：指定旋转后的结果中是否包含NULL值，未指定时不包含NULL值
  - 未指定：不包含NULL值，等同于 EXCLUDE NULLS
  - INCLUDE NULLS：包含NULL值
  - EXCLUDE NULLS：不包含NULL值
- column_list：指定旋转后新列名列表，使用,逗号分隔，unpivot_in_clause 列表中指定的字段数据将作为该列数据
  - ColLabel：字段名称
- pivot_for_clause：指定旋转后新列名列表，以,逗号分隔，unpivot_in_clause 列表中指定的字段名称将作为该列数据
  - ColLabel：字段名称
- unpivot_in_clause：指定数据来源（表、视图、子查询等）的原始列名列表，若仅为单个原始列名，这些名称将成为 pivot_for_clause 列表中的列的值，如果是多个原始列名，将以下划线_拼接原始列名。可选的 AS 子句将原始列名以指定的别名映射到输出列中。pivot_for_clause与unpivot_in_clause中的字段名称或字段别名个数保持一致
  - IN ( unpivot_in_column_list )：指定原始列名列表，使用,逗号分隔
    - column_list_opt_as：
      - const_expr_item：常量表达式
      - opt_as_literal：
        未指定：不设置字段别名，使用原值作为字段名
        AS ( literal_list )：指定字段名列表，使用,逗号分隔
        AS AexprConst：指定字段别名
- alias_clause：指定表的别名，请参阅别名子句

注意

不支持使用多参数的GROUP_CONCAT()聚合函数，推荐使用LISTAGG()，若使用JSON类型相关的聚合函数，其返回结果和数据库的设计有关，请以实际为准
聚合函数的参数只支持按名传参
pivot_clause子句中，如果返回结果是一个计算列（如：EXPR1），须指定别名，否则prepare执行时会出现标识符长度为0的错误
unpivot_in_clause子句中的多个列的数据类型要保持一致，或者相互之间能隐式转换
column和pivot_for_clause中的column个数保持一致
unpivot_in_clause中指定的旋转列的个数建议不要超过1000个，否则影响性能
unpivot_clause子句中，如果返回结果是一个计算列（如：EXPR1），须指定别名，否则prepare执行时会出现标识符长度为 0 的错误

5.3 示例

5.3.1 PIVOT

sql

-- 创建产品销售订单表
SQL> CREATE TABLE tab_sales_orders (
    region VARCHAR(50),
    product VARCHAR(50),
    quarter VARCHAR(50),
    sales_amount NUMERIC(10, 2)
);

-- 插入销售数据
SQL> INSERT INTO tab_sales_orders (region, product, quarter, sales_amount) VALUES
('North', 'ProductA', 'Q1', 10000.00),
('North', 'ProductA', 'Q2', 12000.00),
('North', 'ProductB', 'Q1', 8000.00),
('North', 'ProductB', 'Q2', 9000.00),
('South', 'ProductA', 'Q1', 15000.00),
('South', 'ProductA', 'Q2', 16000.00),
('South', 'ProductB', 'Q1', 7000.00),
('South', 'ProductB', 'Q2', 8500.00);

-- 统计每个地区各产品在不同季度的销售额
SQL> SELECT *
FROM (
    SELECT region, product, quarter, sales_amount
    FROM tab_sales_orders
)
PIVOT (
    SUM(sales_amount)
    FOR quarter IN ('Q1', 'Q2')
) AS pvt ORDER BY region, product;

+--------+----------+-------+-------+
| REGION | PRODUCT  | 'Q1'  | 'Q2'  |
+--------+----------+-------+-------+
| North  | ProductA | 10000 | 12000 |
| North  | ProductB | 8000  | 9000  |
| South  | ProductA | 15000 | 16000 |
| South  | ProductB | 7000  | 8500  |
+--------+----------+-------+-------+

-- 统计不同季度的A产品在不同地区的总销售额和平均销售额
SQL> SELECT *
FROM (
    SELECT region, product, quarter, sales_amount
    FROM tab_sales_orders
)
PIVOT (
    SUM(sales_amount) AS total_sales,
    AVG(sales_amount) AS avg_amount
    FOR (product, region) IN ( ('ProductA', 'North') AS ProductA_North, ('ProductA', 'South') AS ProductA_South )
) AS pvt ORDER BY quarter;

+---------+----------------------------+---------------------------+----------------------------+---------------------------+
| QUARTER | PRODUCTA_NORTH_TOTAL_SALES | PRODUCTA_NORTH_AVG_AMOUNT | PRODUCTA_SOUTH_TOTAL_SALES | PRODUCTA_SOUTH_AVG_AMOUNT |
+---------+----------------------------+---------------------------+----------------------------+---------------------------+
| Q1      | 10000                      | 10000                     | 15000                      | 15000                     |
| Q2      | 12000                      | 12000                     | 16000                      | 16000                     |
+---------+----------------------------+---------------------------+----------------------------+---------------------------+

5.3.2 UNPIVOT

sql

-- 先创建一个包含 PIVOT 结果的临时表
SQL> CREATE OR REPLACE VIEW tab_pivoted_sales AS
SELECT *
FROM (
    SELECT region, product, quarter, sales_amount
    FROM tab_sales_orders
)
PIVOT (
    SUM(sales_amount)
    FOR quarter IN ('Q1' AS Q1, 'Q2' AS Q2, 'Q3' AS Q3, 'Q4' AS Q4)
) AS pvt ORDER BY region, product;

-- 使用UNPIVOT将列转换回行
SQL> SELECT region, product, quarter, sales_amount
FROM tab_pivoted_sales
UNPIVOT (
    sales_amount
    FOR quarter IN (Q1, Q2)
) AS unpvt ORDER BY region, product, quarter;

+--------+----------+---------+--------------+
| REGION | PRODUCT  | QUARTER | SALES_AMOUNT |
+--------+----------+---------+--------------+
| North  | ProductA | Q1      | 10000        |
| North  | ProductA | Q2      | 12000        |
| North  | ProductB | Q1      | 8000         |
| North  | ProductB | Q2      | 9000         |
| South  | ProductA | Q1      | 15000        |
| South  | ProductA | Q2      | 16000        |
| South  | ProductB | Q1      | 7000         |
| South  | ProductB | Q2      | 8500         |
+--------+----------+---------+--------------+

-- 使用UNPIVOT将列转换回行，结果中包含 `NULL` 值
SQL> SELECT *
FROM tab_pivoted_sales
UNPIVOT INCLUDE NULLS (
    ( sales_amount_a, sales_amount_b )
    FOR ( quarter_a, quarter_b ) IN ( (Q1, Q2), (Q3, Q4) )
) AS unpvt ORDER BY region, product;

+--------+----------+-----------+-----------+----------------+----------------+
| REGION | PRODUCT  | QUARTER_A | QUARTER_B | SALES_AMOUNT_A | SALES_AMOUNT_B |
+--------+----------+-----------+-----------+----------------+----------------+
| North  | ProductA | Q3_Q4     | Q3_Q4     | <NULL>         | <NULL>         |
| North  | ProductA | Q1_Q2     | Q1_Q2     | 10000          | 12000          |
| North  | ProductB | Q3_Q4     | Q3_Q4     | <NULL>         | <NULL>         |
| North  | ProductB | Q1_Q2     | Q1_Q2     | 8000           | 9000           |
| South  | ProductA | Q1_Q2     | Q1_Q2     | 15000          | 16000          |
| South  | ProductA | Q3_Q4     | Q3_Q4     | <NULL>         | <NULL>         |
| South  | ProductB | Q3_Q4     | Q3_Q4     | <NULL>         | <NULL>         |
| South  | ProductB | Q1_Q2     | Q1_Q2     | 7000           | 8500           |
+--------+----------+-----------+-----------+----------------+----------------+

FROM 子句 ​

一、主要语法结构 ​

1.1 语法格式 ​

1.2 参数说明 ​

二、别名子句 ​

2.1 语法格式 ​

2.2 参数说明 ​

三、连接子句 ​

3.1 语法格式 ​

3.2 参数说明 ​

四、XML表子句 ​

4.1 语法格式 ​

4.2 参数说明 ​

五、PIVOT/UNPIVOT子句 ​

5.1 语法格式 ​

5.2 参数说明 ​

5.3 示例 ​

5.3.1 PIVOT ​

5.3.2 UNPIVOT ​