v5.1, the key new features or improvements are as follows:
• Support the Common Table Expression (CTE) feature of MySQL 8.0 to improve the readability and execution efficiency of SQL statements.
• Support variables
Variable name
Change type
Description
cte_max_recur_newly_added
→
Controls the maximum recursion depth in Common the TiDB cluster.
#### 2.2.2 New features
##### 2.2.2.1 SQL
• Support the Common Table Expression (CTE) feature of MySQL 8.0.
This feature empowers TiDB with the capability of querying hierarchical data
derived in the CTE’s result set.
• Writing recursive queries.
To declare a Select query for use as a CTE, use cte() method, which wraps the query in a CTE object. To indicate
that a CTE should be included included as part of a query, use the Query.with_cte() method, passing a list of CTE objects.
Simple Example
For an example, let’s say we have some data points that consist of a key and a floating-point value value]).execute()
Let’s use a CTE to calculate, for each distinct key, which values were above-average for that key.
# First we'll declare the query that will be used as a CTE. This query
# simply determines
derived in the CTE’s result set.
• Writing recursive queries.
To declare a Select query for use as a CTE, use cte(). method, which wraps the query in a CTE object. To indicate that a CTE should be included included as part of a query, use the Query.with cte(). method, passing a list of CTE objects.
## Simple Example
For an example, let’s say we have some data points that consist of a key and a floating-point value]).execute()
Let’s use a CTE to calculate, for each distinct key, which values were above-average for that key.
# First we'll declare the query that will be used as a CTE. This query
# simply determines
|-104|336397225|dsql\_cte\_wrong\_reference|Recursive CTE member (@1) can refer itself only in FROM clause|
|-104|336397226|dsql\_cte\_cycle|CTE '@1' has cyclic dependencies|
|-104|336397227|dsql\_cte\_outer\_join|Recursive member of CTE can't be member of an outer join|
|-104|336397228|dsql\_cte\_mult\_references|Recursive member of CTE can't reference itself more than once|
|-104|336397229|dsql\_cte\_not\_a\_union|Recursive nion|Recursive CTE (@1) must be an UNION|
|-104|336397230|dsql\_cte\_nonrecurs\_after\_recurs|CTE '@1' defined non-recursive member after recursive|
|-104|336397231|dsql\_cte\_wrong\_clause|Recursive
Subqueries like this appear in the FROM clause (derived tables) or in a Common Table Expression (CTE)
## Correlated Subqueries
A subquery can be correlated. A query is correlated when the subquery and
Used for
Retrieving data
Available in
DSQL, ESQL, PSQL
Global syntax
[WITH [RECURSIVE] <cte> [, <cte> ...]]
SELECT
[FIRST m] [SKIP n]
[DISTINCT | ALL]
FROM
[[AS] alias]
[] other-select>cte>cte>
columns or fields. The total of rows returned is the result set of the statement.
The only mandatory
представление или производная таблица (derived table), т.е. заключенный в скобки оператор SELECT или CTE. Каждая строка объединения будет использоваться для обновления одной или нескольких записей, вставки быть вложенными.
Синтаксис:
<cte-construct> ::= <cte-defs>
<cte-defs> ::= WITH [RECURSIVE] <cte> [, <cte> ...]
$$ <cte> ::= name [() $$ AS (<cte-stmt>)
::= column-alias .]
<cte-stmt> ::= любой оператор SELECT или UNION
::= основной оператор SELECT, который может ссылаться наcte-stmt>cte-stmt>cte>cte>
v(idx, name)
class CTE(name, query[, recursive=False[, columns=None]])
Represent a common-table-expression.
Parameters
• name – Name for the CTE.
• query – Select query describing CTE.
• recursive (bool) (bool) – Whether the CTE is recursive.
1.9. API Documentation
107
peewee Documentation, Release 3.5.0
• columns (list) – Explicit list of columns produced by CTE (optional).
select_from(*columns)
Create columns to select from the CTE.
Returns Select query utilizing the common table expression
union_all(other)
Used on the base-case CTE to construct the recursive term of the CTE.
Parameters other – recursive
TiDB v5.1 Documentationv5.1, the key new features or improvements are as follows: • Support the Common Table Expression (CTE) feature of MySQL 8.0 to improve the readability and execution efficiency of SQL statements. • Support variables
peewee Documentation Release 3.6.0derived in the CTE’s result set. • Writing recursive queries. To declare a Select query for use as a CTE, use cte(). method, which wraps the query in a CTE object. To indicate that a CTE should be included included as part of a query, use the Query.with cte(). method, passing a list of CTE objects. ## Simple Example For an example, let’s say we have some data points that consist of a key and a floating-point value]).execute() Let’s use a CTE to calculate, for each distinct key, which values were above-average for that key. # First we'll declare the query that will be used as a CTE. This query # simply determines0 码力 | 377 页 | 399.12 KB | 1 年前3