A The meaning of with AS
The with as phrase, also called the subquery section (subquery factoring), lets you do a lot of things by defining an SQL fragment that will be used by the entire SQL statement. Sometimes it is to make the SQL statement more readable, or it may be in different parts of union all, as part of providing data.
Particularly useful for union all. Because each part of union all may be the same, the cost is too high if each part is executed again, so you can use the with as phrase, just do it once. If the table name defined by the with as phrase is called more than two times, the optimizer automatically puts the data obtained by the with as phrase into a temp table, and if it is only invoked once, it does not. The hint materialize is to force the data in the with as phrase into a global temporary table. Many queries can improve speed in this way.
two. How to use
First look at one of the nested query statements below:
Copy Code code as follows:
SELECT * FROM person. StateProvince where Countryregioncode in
(select Countryregioncode from person.) CountryRegion where Name like ' c% ')
The query statement above uses a subquery. Although this SQL statement is not complex, it makes SQL statements very difficult to read and maintain if there are too many layers of nesting. Therefore, you can also use table variables to solve this problem, the SQL statement is as follows:
Copy Code code as follows:
Declare @t table (Countryregioncode nvarchar (3))
Insert into @t (countryregioncode), select Countryregioncode from person. CountryRegion where Name like ' c% ')
SELECT * FROM person. StateProvince where Countryregioncode
In (SELECT * from @t)
While 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 also brings another problem, the loss of performance. Because table variables actually use temporary tables, which increases additional I/O overhead, table variables are not as well suited to large data and frequently queried situations. To do this, there is another solution in SQL Server 2005, a common table expression (CTE), in which the use of a CTE enables the maintainability of the SQL statement, while the CTE is much more efficient than the table variable.
The following is the syntax for a CTE:
Copy Code code as follows:
[With <common_table_expression> [, N]]
<common_table_expression>::=
Expression_name [(column_name [, N])]
As
(cte_query_definition)
Now using a CTE to solve the problem above, the SQL statement is as follows:
Copy Code code as follows:
With
CR as
(
Select Countryregioncode from person. CountryRegion where Name like ' c% '
)
SELECT * FROM person. StateProvince where Countryregioncode in (SELECT * from CR)
Where CR is a common table expression, which is similar to a table variable in use, except that SQL Server 2005 differs in how common table expressions are handled.
The following points should be noted when using a CTE:
1. The CTE must be followed directly by an SQL statement using a CTE (such as SELECT, Insert, UPDATE, and so on), otherwise the CTE will fail. If the following SQL statement will not work with a CTE:
Copy Code code as follows:
With
CR as
(
Select Countryregioncode from person. CountryRegion where Name like ' c% '
)
SELECT * FROM person. CountryRegion--This SQL statement should be removed
--The SQL statement using the CTE should be immediately behind the associated CTE-
SELECT * FROM person. StateProvince where Countryregioncode in (SELECT * from CR)
2. A CTE may also be followed by a CTE, but only one with, separated by commas (,) between multiple CTE, as shown in the following SQL statement:
Copy Code code as follows:
With
Cte1 as
(
SELECT * FROM table1 where name like ' abc% '
),
Cte2 as
(
SELECT * from table2 where ID > 20
),
Cte3 as
(
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 a CTE is the same as a data table or view, the SQL statement immediately following the CTE is still using a CTE, and of course, the following SQL statement uses a datasheet or view, as shown in the SQL statement:
Copy Code code as follows:
--Table1 is an actual table.
With
Table1 AS
(
SELECT * from persons where age < 30
)
SELECT * FROM table1--using a common table expression named table1
SELECT * FROM table1--using a datasheet named table1
4. A CTE can refer to itself, or it can reference a CTE that is predefined in the same with clause. Forward references are not allowed.
5. The following clauses cannot be used in CTE_query_definition:
(1) COMPUTE or COMPUTE by
(2) Order by (unless a TOP clause is specified)
(3) into
(4) OPTION clause with query hint
(5) for XML
(6) for BROWSE
6. If a CTE is used in a statement that is part of a batch, the statement before it must end with a semicolon, as shown in the following SQL:
Copy Code code as follows:
declare @s nvarchar (3)
Set @s = ' c% '
; --You must add a semicolon
With
T_tree as
(
Select Countryregioncode from person. CountryRegion where Name like @s
)
SELECT * FROM person. StateProvince where Countryregioncode in (SELECT * from T_tree)
A CTE can also be called recursively, in addition to simplifying nested SQL statements.
Microsoft introduced a CTE (Common Table Expression) from SQl2005 to harden T-SQL. This is a good thing similar to a non-persistent view.
Follow the MSDN introduction
1. A common table expression (CTE) can be considered to be a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and is valid only for the duration of the query. Unlike a derived table, a CTE can be referenced and can be referenced multiple times in the same query.
A CTE can be used to:
Create a recursive query. For more information, see recursive queries that use common table expressions.
Replace the view when you do not need to use the general view, that is, you do not have to store the definition in the metadata.
Enables grouping by columns derived from a scalar nested SELECT statement, or by an nondeterministic function or a function that has external access.
References the generated table more than once in the same statement.
Using a CTE can gain the benefits of improving readability and easy maintenance of complex queries. Queries can be divided into individual blocks, simple blocks, and logical building blocks. These simple blocks can then be used to generate more complex temporary CTE until the final result set is generated. You can define a CTE in a user-defined routine, such as a function, stored procedure, trigger, or view.
2. Common table Expressions (CTE) have an important advantage in being able to reference themselves to create a recursive CTE. A recursive CTE is a common table expression that repeats an initial CTE to return a subset of data until the full result set is obtained. When a query references a recursive CTE, it is called a recursive query. Recursive queries are typically used to return hierarchical data, such as displaying an employee or BOM proposal in an organization chart where the parent product has one or more components, and those components may have subcomponents, or components of other parent products.
A recursive CTE can greatly simplify the code that is required to run a recursive query in a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. In earlier versions of SQL Server, recursive queries often required the use of temporary tables, cursors, and logic to control the flow of recursive steps. For more information about common table expressions, see Working with common table expressions.
Examples here are as follows:
In order to describe the convenience, invited the month specially to enumerate a common autocorrelation table
The table structure is as follows:
Table structure
Copy Code code as follows:
CREATE TABLE [dbo]. [Categoryself] (
[Pkid] [INT] IDENTITY (1,1) not NULL,
[C_name] [nvarchar] () not NULL,
[C_level] [INT] Not NULL,
[C_code] [nvarchar] (255) NULL,
[C_parent] [INT] Not NULL,
[Inserttime] [DateTime] Not NULL,
[Insertuser] [nvarchar] (m) NULL,
[UpdateTime] [DateTime] Not NULL,
[UpdateUser] [nvarchar] (m) NULL,
[Sortlevel] [INT] Not NULL,
[Currstate] [smallint] Not NULL,
[F1] [INT] Not NULL,
[F2] [nvarchar] (255) NULL
CONSTRAINT [pk_objectcategoryself] PRIMARY KEY CLUSTERED
(
[Pkid] Asc
With (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = ON) O N [PRIMARY]
) on [PRIMARY]
Go
And then insert some test data
Copy Code code as follows:
Insert
INSERT into [categoryself] ([C_name],[c_level], [c_code],[c_parent], [Inserttime], [Insertuser], [UpdateTime], [ UpdateUser], [Sortlevel], [currstate], [F1], [F2])
Select ' Classify 1 ', 1, ' 0 ', 0,getdate (), ' TestUser ', DATEADD (Dd,1,getdate ()), ' Crackuser ', 13,0,1, ' invite monthly notes ' UNION ALL
Select ' Classify 2 ', 1, ' 0 ', 0,getdate (), ' TestUser ', DATEADD (Dd,78,getdate ()), ' Crackuser ', 12,0,1, ' invite monthly notes ' UNION ALL
Select ' Classify 3 ', 1, ' 0 ', 0,getdate (), ' TestUser ', DATEADD (Dd,6,getdate ()), ' Crackuser ', 10,0,1, ' invite monthly notes ' UNION ALL
Select ' Classify 4 ', 2, ' 1 ', 1,getdate (), ' TestUser ', DATEADD (Dd,75,getdate ()), ' Crackuser ', 19,0,1, ' invite monthly notes ' UNION ALL
Select ' Classify 5 ', 2, ' 2 ', 2,getdate (), ' TestUser ', DATEADD (Dd,3,getdate ()), ' Crackuser ', 17,0,1, ' invite monthly notes ' UNION ALL
Select ' Classify 6 ', 3, ' 1/4 ', 4,getdate (), ' TestUser ', DATEADD (Dd,4,getdate ()), ' Crackuser ', 16,0,1, ' invite monthly notes ' UNION ALL
Select ' Classify 7 ', 3, ' 1/4 ', 4,getdate (), ' TestUser ', DATEADD (Dd,5,getdate ()), ' Crackuser ', 4,0,1, ' invite monthly notes ' UNION ALL
Select ' Classify 8 ', 3, ' 2/5 ', 5,getdate (), ' TestUser ', DATEADD (Dd,6,getdate ()), ' Crackuser ', 3,0,1, ' invite monthly notes ' UNION ALL
Select ' Classify 9 ', 4, ' 1/4/6 ', 6,getdate (), ' TestUser ', DATEADD (Dd,7,getdate ()), ' Crackuser ', 5,0,1, ' invite monthly notes ' UNION ALL
Select ' Classified ', 4, ' 1/4/6 ', 6,getdate (), ' TestUser ', DATEADD (Dd,7,getdate ()), ' Crackuser ', 63,0,1, ' invite monthly notes ' UNION ALL
Select ' Categories ', 4, ' 1/4/6 ', 6,getdate (), ' TestUser ', DATEADD (Dd,8,getdate ()), ' Crackuser ', 83,0,1, ' invite monthly notes ' UNION ALL
Select ' Categories ', 4, ' 2/5/8 ', 8,getdate (), ' TestUser ', DATEADD (Dd,10,getdate ()), ' Crackuser ', 3,0,1, ' invite monthly notes ' UNION ALL
Select ' Classified ', 4, ' 2/5/8 ', 8,getdate (), ' TestUser ', DATEADD (Dd,15,getdate ()), ' Crackuser ', 1,0,1, ' Invite month notes '
A typical scenario is: In this autocorrelation table, the query takes a Pkid 2 taxonomy containing all subcategories. Perhaps in many cases, we have to use temporary tables/table variables/cursors and so on. Now that we have a CTE, it's a lot simpler.
Copy Code code as follows:
CTEDemo1
With Simplerecursive (C_name, Pkid, c_code,c_parent)
As
(SELECT c_name, Pkid, c_code,c_parent from categoryself WHERE pkid = 2
UNION All
SELECT P.c_name, P.pkid, p.c_code,p.c_parent
From Categoryself P INNER JOIN
Simplerecursive A on a.pkid = p.c_parent
)
SELECT Sr. C_name as C_name, C.c_name as C_PARENTNAME,SR. C_code as C_parentcode
From Simplerecursive SR INNER join Categoryself C
on Sr. C_parent=c.pkid
The results of the query are as follows: C_name c_parentname C_parentcode
Category 5 Classification 2 2
Category 8 Classification 5 2/5
Category 12 Classification 8 2/5/8
Category 13 Classification 8 2/5/8
How do you feel? If I just want to query the second level instead of the default infinite query,
You can add an option (Maxrecursion 5) to the above SQL, and note that 5 means you don't look down on the 5th floor. If you only want to find the second layer, but the actual result is three levels, there will be errors,
MSG 530, level, State 1, line 1
The statement terminated. The maximum recursion 1 has been exhausted before.
This can be resolved by the Where condition, which guarantees no errors, looking at the following SQL statement:
CTEDemo2
Copy Code code as follows:
With Simplerecursive (C_name, Pkid, C_code,c_parent,sublevel)
As
(SELECT c_name, Pkid, c_code,c_parent,0 from categoryself WHERE pkid = 2
UNION All
SELECT P.c_name, P.pkid, p.c_code,p.c_parent,sublevel+1
From Categoryself P INNER JOIN
Simplerecursive A on a.pkid = p.c_parent
)
SELECT Sr. C_name as C_name, C.c_name as C_PARENTNAME,SR. C_code as C_parentcode
From Simplerecursive SR INNER join Categoryself C
on Sr. C_parent=c.pkid
where sublevel<=2
Query results:
C_name C_parentname C_parentcode
Category 5 Classification 2 2
Category 8 Classification 5 2/5
Of course, we are not saying that a CTE is omnipotent. A good table design can also solve specific problems in some way. Here's a general SQL implementation of this requirement.
Note: The above table has a field is very important, that is, C_code, encoding, format such as "1/2", "2/5/8" means that the classification of the superior classification is 1/2,2/5/8
In this way, our query is much simpler, and the query with the Pkid 2 category contains all the subcategories:
Copy Code code as follows:
Select C_name as C_name (select top 1 c_name from categoryself s where C.c_parent=s.pkid) as c_parentname,c_code as C_par Entcode
From categoryself C where c_code like ' 2/% '
Queries with Pkid 2 contain all subcategories with a level not greater than 3
Copy Code code as follows:
Select C_name as C_name (select top 1 c_name from categoryself s where C.c_parent=s.pkid) as c_parentname,c_code as C_par Entcode
From categoryself C where c_code like ' 2/% ' and c_level<=3
Query results above, omitted. Here we see that sometimes good table structure design is very important.
Invite month to 2009.10.23 1:36 to complete the sharing.
Some people are concerned about performance issues. No tests have been done at this time. A millions test report will be attached later. However, there are two points of understanding invited month forgot to add:
One, the CTE is actually object-oriented, the basis of the operation is the CLR. A good illustration is that the WITH query statement is case-sensitive in the case of a field. That is, "C_code" and "C_code" are not the same, the latter will be an error. This is different from a normal SQL statement.
The example of this application is to simplify the business logic, even if the performance is not good, but the temporary table/table variables/cursors, such as traditional processing is a business-level simplification or optimization.
A common table expression (CTE) can be considered a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and is valid only for the duration of the query. Unlike a derived table, a CTE can be referenced and can be referenced multiple times in the same query.
A CTE can be used to:
Create a recursive query.
Replace the view when you do not need to use the general view, that is, you do not have to store the definition in the metadata.
Enables grouping by columns derived from a scalar nested SELECT statement, or by an nondeterministic function or a function that has external access.
References the generated table more than once in the same statement.
Using a CTE can gain the benefits of improving readability and easy maintenance of complex queries. Queries can be divided into individual blocks, simple blocks, and logical building blocks. These simple blocks can then be used to generate more complex temporary CTE until the final result set is generated.
You can define a CTE in a user-defined routine, such as a function, stored procedure, trigger, or view.
The structure of a CTE
A CTE consists of an expression name that represents a CTE, an optional column list, and a query that defines a CTE. Once a CTE is defined, it can be referenced in a SELECT, INSERT, UPDATE, or DELETE statement, just like a reference table or view. A CTE can also be used in the CREATE VIEW statement as part of the definition of a SELECT statement.
The basic grammatical structure of a CTE is as follows:
With Expression_name [(column_name [,... n])]
As
(cte_query_definition)
The list of column names is optional only if a different name is provided for all result columns in the query definition.
The statement running the CTE is:
SELECT <column_list>
From Expression_name
Example:
Copy Code code as follows:
With S_name as
(
Select S.name, sc.c,sc.grade from SQL Server student as S,SC
where s.s#=sc.s#
)
SELECT * FROM S_name
The following points should be noted when using a CTE:
1. The CTE must be followed directly by an SQL statement using a CTE (such as SELECT, Insert, UPDATE, and so on), otherwise the CTE will fail.
2. A CTE may also be followed by a CTE, but only one with, separated by commas (,) in the middle of multiple CTE.
3. If the expression name of a CTE is the same as a data table or view, the SQL statement immediately following the CTE is still using a CTE, and of course, the following SQL statement uses a datasheet or view.
4. A CTE can refer to itself (a recursive query), or it can reference a CTE that is predefined in the same with clause. Forward references are not allowed.
5. The following clauses cannot be used in CTE_query_definition:
(1) COMPUTE or COMPUTE by
(2) Order by (unless a TOP clause is specified)
(3) into
(4) OPTION clause with query hint
(5) for XML
(6) for BROWSE
6. If a CTE is used in a statement that is part of a batch, the statement before it must end with a semicolon.
