SQL common table expression (CTE)

1. Concept

A common table expression (Common table expression) is an attribute introduced after the SQL SERVER 2005 version. A CTE can be thought of as a temporary result set that can be referenced more than once in the next Select,insert,update,delete,merge statement. Use common expressions to make statements clearer and more concise.

In addition, according to Microsoft's description of the CTE benefits, it boils down to four points:

• Recursive common table Expressions (CTE) can be defined
• A CTE can make it more concise when you don't need to reference the result set as a view by multiple places
• A GROUP by statement can directly act on a scalar column derived from a subquery
• Common table Expressions (CTE) can be referenced more than once in a single statement

2. Data

name    Course    score Zhang San    language      Zhang San    mathematics      Zhang San    physics      John Doe    language      John Doe    math      John Doe    Physical     94

3. Nested query statements

Select *  from where inch (Selectfromwhere score >+)

The query statement above uses a subquery. Although this SQL statement is not complex, it can make SQL statements very difficult to read and maintain if there are too many nested hierarchies.

Therefore, you can also use table variables to solve this problem, the SQL statement is as follows:

 declare  @t  table  (Score int   insert  into  @t  Score (select  score from  TB where  score >  90  )  select  Span style= "COLOR: #808080" >*  from  TB where  score in  (select  *  from  @t ) 

Although the above SQL statement is more complex than the first, it puts the subquery in the table variable @t, which makes the SQL statement easier to maintain, but it also introduces another problem, the loss of performance.

Because table variables actually use temporary tables, which increases the additional I/O overhead, table variables do not work well for large data volumes and queries frequently.

To do this, another solution is provided in SQL Server, which is a common table expression (CTE) that uses a CTE to make the SQL statement maintainable, while the CTE is much more efficient than the table variable.

 with  as (    Select fromwhere score >+)Select *  from where inch (Select* from CR)

4, in the use of CTE should pay attention to the following points:

4.1. CTE must be followed directly with the SQL statement using the CTE (such as SELECT, INSERT, UPDATE, etc.), otherwise the CTE will be invalidated. The CTE will not work correctly as in the following SQL statement:

 withCr as (    SelectScores fromTbwhereScores> -)Select *  fromTb--you should remove this SQL statementSelect *  fromTbwhereScoresinch(Select *  fromCr--the SQL statement using the CTE should immediately follow the relevant CTE

4.2. CTE can also be followed by other CTE, but only with one with, multiple CTE is separated by commas (,), as shown in the following SQL statement

 withCr1 as(    Select *  fromTB), Cr2 as(    Select *  fromTB), CR3 as(    Select *  fromTB)SelectA.*  fromCR1 A, Cr2 B, CR3 cwhereA. Score=B. Score andA. Score=C. Score

4.3. If the expression name of the CTE is the same as a data table or view, the SQL statement immediately following the CTE is still using the CTE, and of course, the subsequent SQL statement uses the data table or view, as shown in the following SQL statement:

 with  as (    Select from Student)Select* from

4.4. If the CTE is used in a statement that is part of a batch, then the statement before it must end with a semicolon, as shown in the following SQL:

Declare @t Table(Scoreint)Insert  into @tScore (SelectScores fromTbwhereScores> -);--You must add a semicolon withTr as(    SelectScores fromTbwhereScoresinch(Select *  from @t))Select *  fromTr

SQL common table expression (CTE)