github.com/mithrandie/csvq@v1.18.1/docs/_posts/2006-01-02-common-table-expression.md (about)

     1  ---
     2  layout: default
     3  title: Common Table Expression - Reference Manual - csvq
     4  category: reference
     5  ---
     6  
     7  # Common Table Expression
     8  
     9  A Common Table Expression in a _with clause_ declare an inline table that can be referenced in a single query.
    10  You can use the views in a [Select Query]({{ '/reference/select-query.html' | relative_url }}), [Insert Query]({{ '/reference/insert-query.html' | relative_url }}), [Update Query]({{ '/reference/update-query.html' | relative_url }}), or [Delete Query]({{ '/reference/delete-query.html' | relative_url }}).
    11  
    12  ## Syntax
    13  
    14  ```sql
    15  with_clause
    16    : WITH common_table_expression [, common_table_expression ...]
    17  
    18  common_table_expression
    19    : [RECURSIVE] table_name [(column_name [, column_name ...])] AS (select_query)
    20  ```
    21  
    22  _table_name_
    23  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
    24  
    25  _column_name_
    26  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
    27  
    28  _select_query_
    29  : [select_query]({{ '/reference/select-query.html' | relative_url }})
    30  
    31  ### Recursion
    32  
    33  If you specified a RECURSIVE keyword, the _select_query_ in the _common_table_clause_ can retrieve the result recursively.
    34  A RECURSIVE keyword is usually used with a [UNION]({{ '/reference/set-operators.html#union' | relative_url }}) operator.
    35  
    36  ```sql
    37  WITH
    38    RECURSIVE table_name [(column_name [, column_name ...])]
    39    AS (
    40      base_select_query
    41      UNION [ALL]
    42      recursive_select_query
    43    )
    44  ```
    45  
    46  At first, the result set of the _base_select_query_ is stored in the _temporary view_ for recursion.
    47  Next, the _recursive_select_query_ that refer to the _temporary view_ is excuted and the _temporary view_ is replaced by the result set of the _recursive_select_query_.
    48  The execution of the _recursive_select_query_ is iterated until the result set is empty.
    49  All the result sets are combined by the [UNION]({{ '/reference/set-operators.html#union' | relative_url }}) operator.
    50  
    51  Example:
    52  ```sql
    53  WITH RECURSIVE t (n)
    54    AS (
    55      SELECT 1
    56      UNION ALL
    57      SELECT n + 1
    58        FROM t
    59       WHERE n < 5
    60    )
    61  SELECT n FROM t;
    62  
    63  
    64  /* Result Set
    65  +---+
    66  | n |
    67  +---+
    68  | 1 |
    69  | 2 |
    70  | 3 |
    71  | 4 |
    72  | 5 |
    73  +---+
    74  */
    75  ```