Delete the first 100 SQL statements with

With CTE as (select top 50 * From tablename)

Delete from CTE

The with as phrase, also known as subquery factoring, allows you to do a lot of things and define an SQL segment, which will be used by the entire SQL statement. Sometimes, it is to make the SQL statement more readable, or it may be in the Union
Different parts of all, as part of providing data.

Especially useful for Union all. Because each part of Union all may be the same, but if each part is executed once, the cost is too high, so you can use
As phrase, you only need to execute it once. If the table name defined by the with as phrase is called more than twice, the optimizer automatically puts the data obtained by the with as phrase into a temp table. If it is called only once, no. The message "materialize" indicates that the
The data in the as phrase is placed in a global temporary table. This method improves the speed of many queries.

The syntax of CTE is as follows:

 

[With <common_table_expression> [, N]

<Common_table_expression >::=

Expression_name [(column_name [, N])]

As

(Cte_query_definition)

With

Cr

(

Select countryregioncode from person. countryregion where name like 'C %'

)

 

Select * From person. stateprovince where countryregioncode in (select * from Cr)

 

Cr is a common table expression, which is similar to a table variable in use, but SQL Server 2005 has different processing methods for common table expressions.

 

Pay attention to the following points when using CTE:

1. The CTE must be followed by the SQL statements that use the CTE (such as select, insert, and update). Otherwise, the CTE will become invalid. The following SQL statement cannot use CTE normally:

 

 

With

Cr

(

Select countryregioncode from person. countryregion where name like 'C %'

)

Select * From person. countryregion --
Remove this SQL statement

--
The SQL statement using CTE should be followed by the relevant CTE.
--

Select * From person. stateprovince where countryregioncode in (select * from Cr)

 

 

2. The CTE can be followed by other CTE, but only one with can be used. Multiple CTE are separated by commas (,), as shown in the following SQL statement:

 

 

 

With

Cte1

(

Select * From Table1 where name like 'abc %'

),

Cte2

(

Select * From Table2 where ID> 20

),

Cte3

(

Select * From table3 where price <100

)

Select a. * From cte1 A, cte2 B, cte3 c Where a. ID = B. ID and A. ID = C. ID

 

3.
If the expression name of the CTE is the same as that of a data table or view, the SQL statement followed by the CTE still uses the CTE. Of course, the following SQL statement uses a data table or view, as shown in the following SQL statement:

 

 

-- Table1 is an existing table.

 

With

Table1

(

Select * from persons where age <30

)

Select * From Table1 --
A public table expression named Table1 is used.

Select * From Table1 --
Table 1 is used.

 

4. CTE
You can reference yourself or in the same
The pre-defined CTE in the clause. Forward references are not allowed.

 

5.
Cannot Be In cte_query_definition
Use the following clause:

 

(1) compute
Or compute

 

(2) Order by (unless specified
Top clause)

 

(3)

 

(4) option with query prompt
Clause

 

(5) For XML

 

(6) For browse

 

6.
If you
When used in a statement that is part of the batch processing, the statement before it must end with a semicolon, as shown in the following SQL statement:

 

Declare @ s nvarchar (3)

Set @ s = 'C %'

;--
Credit points required

With

T_tree

(

Select countryregioncode from person. countryregion where name like @ s

)

Select * From person. stateprovince where countryregioncode in (select * From t_tree)

 

In addition to simplifying nested SQL statements, CTE can also be called recursively,