Sql. With AS. Common table expression (CTE)

Source: Internet
Author: User
Tags getdate

A Meaning of with AS
The with as phrase, also called the subquery section (subquery factoring), allows you to do many things, defining a 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.
Especially useful for union all. Because each part of union all may be the same, but if each part goes through it, the cost is too high, so you can use the with as phrase, as long as you execute it again. If the table name defined by the with as phrase is called more than two times, the optimizer automatically places the data obtained from the with as phrase into a temp table, if it is called only once. The hint materialize, however, 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
Let's look at one of the following nested query statements:

12 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 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:

1234 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)

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 2005, 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 a table variable.

Here is the syntax for the CTE:

12345 [ WITH <common_table_expression> [ ,n ] ]<common_table_expression>::=        expression_name [ ( column_name [ ,n ] ) ]    AS        ( CTE_query_definition )

Now using the CTE to solve the above problem, the SQL statement is as follows:

1234567 withcr 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 that 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 with the SQL statement that uses the CTE (such as SELECT, Insert, UPDATE, and so on), otherwise the CTE will fail. The CTE will not work correctly as in the following SQL statement:

12345678 withcr as(    select CountryRegionCode from person.CountryRegion where Name like ‘C%‘)select * from person.CountryRegion  -- 应将这条SQL语句去掉-- 使用CTE的SQL语句应紧跟在相关的CTE后面 --select * from person.StateProvince where CountryRegionCode in(select * from cr)

2. The CTE can also be followed by other CTE, but only one with, the middle of multiple CTE separated by commas (,), as shown in the following SQL statement:

1234567891011121314 withcte1 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 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:

--table1 is a table that actually exists

1234567 withtable1 as(    select * from persons where age < 30)select * from table1  --  使用了名为table1的公共表表达式select * from table1  --  使用了名为table1的数据表

4. The CTE can refer to itself, or it can refer to a pre-defined CTE 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 the TOP clause is specified)

(3) into

(4) OPTION clause with query hint

(5) for XML

(6) for BROWSE

6. 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:

123456789 declare @s nvarchar(3)set @s = ‘C%‘;  -- 必须加分号witht_tree as(    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, the CTE can also make recursive calls.

Example Show

User table

123 SELECT CONVERT(varchar(100), [RegistDate], 23) dates,COUNT(*) addsuersFROM [FenxCloudZj].[dbo].[tourol_B2CUser] where [RegistDate]>DATEADD(M,-1,getdate())group by CONVERT(varchar(100), [RegistDate], 23)

Next, use the CTE to stitch together

123456789101112131415 /****** Script for SelectTopNRows command from SSMS  ******/WITH CET AS(    SELECT CONVERT(varchar(100), [RegistDate], 23) dates,COUNT(*) addusers    FROM [FenxCloudZj].[dbo].[tourol_B2CUser] where [RegistDate]>DATEADD(M,-1,getdate())    group by CONVERT(varchar(100), [RegistDate], 23))SELECT CONVERT(varchar(22),TABLE4.DATES, 102 ) dates,ISNULL(TABLE5.adduers,0) adduers FROM(    select DATEADD(D,table2.number,table1.mindates) dates    from (select MIN(dates) mindates,MAX(dates) maxdates from CET) table1,    master..spt_values table2    where table2.type=‘p‘and DATEADD(D,table2.number,table1.mindates)<=table1.maxdates)TABLE4 LEFT JOIN CET TABLE5 ON TABLE4.DATES=TABLE5.DATES 

Sql. With AS. Common table Expression (CTE) (GO)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.