SQL Server series: Common table Expression CTE

1 CTE syntax with keyword

A common table expression (Common table Express, CTE) that defines the derived table at the top of the query. To start creating a query using a CTE, you can use the WITH keyword.

　　CTE Syntax:

 with <expression_name>[(column_name [,... n])]    as      (cte_query_definition)    [, <another_expression>]  <query>

The CTE is first provided with a name similar to the alias used to derive the table. You can then provide the list of column names that the CTE will return, which is optional if the CTE specifies all of its returned columns. Finally, add the definition of the CTE query in parentheses, and finally add the main query that uses the CTE.

　　The statement preceding the WITH keyword must use a semicolon (;) end.

2 Using multiple CTE

3 Recursive CTE

SQL Server series: Common table Expression CTE