|DB2 现在支持在子查询和全查询中使用 ORDER BY。
|以下是已修改的全查询的部分语法图,它显示了 order-by-clause 的位置。
|>>-+-subselect---------+----------------------------------------> | +-(fullselect)------+ | '-| values-clause |-' | | .----------------------------------------------. | V | |>----+------------------------------------------+-+-------------> | '-+-UNION---------+--+-subselect---------+-' | +-UNION ALL-----+ +-(fullselect)------+ | +-EXCEPT--------+ '-| values-clause |-' | +-EXCEPT ALL----+ | +-INTERSECT-----+ | '-INTERSECT ALL-' | |>--+-----------------+----------------------------------------->< | '-order-by-clause-' | |
|包含 ORDER BY 子句的全查询不能以下对象中指定:
|全查询中的 ORDER BY 子句不会影响查询返回的行的次序。如果在最外部全查询中指定 |ORDER BY 子句,它只会影响返回的行的次序。
|以下是已修改的子查询的完整语法图,它显示了 order-by-clause 和 fetch-first-clause 的位置。
|>>-select-clause--from-clause--+--------------+-----------------> | '-where-clause-' | |>--+-----------------+--+---------------+-----------------------> | '-group-by-clause-' '-having-clause-' | |>--+-----------------+----------------------------------------->< | '-order-by-clause-' | |
|子查询的子句是以以下顺序处理的:
|不能在下列对象中指定包含 ORDER BY 的子查询:
|例如,以下语句无效(SQLSTATE 428FJ SQLCODE -20211):
|SELECT * FROM T1 | ORDER BY C1 |UNION |SELECT * FROM T2 | ORDER BY C1
|以下示例有效:
|(SELECT * FROM T1 | ORDER BY C1) |UNION |(SELECT * FROM T2 | ORDER BY C1)
|子查询中的 ORDER BY 子句不会影响查询返回的行的次序。如果在最外部全查询中指定 ORDER BY |子句,它只会影响返回的行的次序。
|以下是已修改的 order-by-clause 的完整语法图。
| .-,------------------------------. | V .-ASC--. | |>>-ORDER BY----+-sort-key--+------+---------+-+---------------->< | | '-DESC-' | | '-ORDER OF--table-designator-' | |sort-key | ||--+-simple-column-name--+--------------------------------------| | +-simple-integer------+ | '-sort-key-expression-' | ||
|注意,此表单在全查询中是不允许的(不同于全查询的退化表单)。 |例如,以下语句无效:
|(SELECT C1 FROM T1 | ORDER BY C1) |UNION |SELECT C1 FROM T2 | ORDER BY ORDER OF T1
|以下示例有效:
|SELECT C1 FROM | (SELECT C1 FROM T1 | UNION | SELECT C1 FROM T2 | ORDER BY C1 ) AS UTABLE |ORDER BY ORDER OF UTABLE|
|以下是已修改的 select-statement 的完整语法图:
|>>-+-----------------------------------+--fullselect------------> | | .-,-----------------------. | | | V | | | '-WITH----common-table-expression-+-' | |>--fetch-first-clause--*--+--------------------+----------------> | +-read-only-clause---+ | | (1) | | '-update-clause------' | |>--*--+---------------------+--*--+--------------+------------->< | '-optimize-for-clause-' '-WITH--+-RR-+-' | +-RS-+ | +-CS-+ | '-UR-' | |
|注:
|语法
| .-,-------------. | V | |>>-select-clause--INTO----host-variable-+--from-clause----------> | |>--+--------------+--+-----------------+--+---------------+-----> | '-where-clause-' '-group-by-clause-' '-having-clause-' | |>--+-----------------+--+--------------+----------------------->< | '-order-by-clause-' '-WITH--+-RR-+-' | +-RS-+ | +-CS-+ | '-UR-' | |
|以下是 OLAP 函数的部分语法图,显示已修改的 window-order-clause。
|window-order-clause | | .-,--------------------------------------------. | V .-| asc option |--. | ||--ORDER BY----+-sort-key-expression--+-----------------+-+-+---| | | '-| desc option |-' | | '-ORDER OF--table-designator---------------' | |asc option | | .-NULLS LAST--. ||--ASC--+-------------+-----------------------------------------| | '-NULLS FIRST-' | |desc option | | .-NULLS FIRST-. ||--DESC--+-------------+----------------------------------------| | '-NULLS LAST--' | ||