T-SQL With keyword common table expressions (CTE)

Transfer http://www.cnblogs.com/wwan/archive/2011/02/24/1964279.html

The SELECT statement is the last logical step for SQL statement processing. Therefore, the following query may cause an error:

 
SelectYear(Orderdate)AsOrderyear,Count(DistinctCustomerid)AsNumcustsFromDBO.OrdersGroupOrderyear;
 

Because group by is performed before Select, The orderyear column is not formed at that time.

If the query is successful, modify it as follows:

SelectOrderyear,Count(DistinctCustomerid)AsNumcustsFrom(SelectYear(Orderdate)AsOrderyear,CustomeridFromDBO.Orders)AsDGroupOrderyear;
 

There is also a special Syntax:

SelectOrderyear,Count(DistinctCustomerid)AsNumcustsFrom(SelectYear(Orderdate),CustomeridFromDBO.Orders)AsD(Orderyear,Customerid)GroupOrderyear;
 

In the eyes of the author, he is very fond of this writing, because it is clearer, clearer, and easier to maintain.

Using Parameter targeting to generate a batch of results in a query is nothing to say.

Nested queries are logically executed from the inside out.

Multiple references. It is possible that your SQL statement contains a join combination after multiple queries from a table. For example, if you want to compare the number of customers each year with the number of customers in the previous year, you must join two instances of the same table. This is inevitable.

Common table expressions (CTE)

CTE is a new table type added in sql2005.

It is defined as follows:

With cte_name

As

(

Cte_query

)

Outer_query_refferring to_cte_name;

Note: Because the With keyword is already included in the standard T-SQL language, for distinction, the CTE adds ';' At the end of the statement as the stop character.

CTE instance 1 (result set alias)

 
WithCAs(SelectYear(Orderdate)AsOrderyear,CustomeridFromDBO.Orders)SelectOrderyear,Count(DistinctCustomerid)AsNumcustsFromCGroupOrderyear;
 

Of course, I personally recommend the following statement:

WithC(Orderyear,Customerid)As(SelectYear(Orderdate),CustomeridFromDBO.Orders)SelectOrderyear,Count(DistinctCustomerid)AsNumcustsFromCGroupOrderyear;
 

CTE instance 2 (multiple ctes)

WithC1As(SelectYear(Orderdate)AsOrderyear,CustomeridFromDBO.Orders),C2As(SelectOrderyear,Count(DistinctCustomerid)AsNumcustsFromC1GroupOrderyear)SelectOrderyear,NumcustsFromC2WhereNumcusts>70;
 

CTE instance 3 (multiple references)

WithYearlycountAs(SelectYear(Orderdate)AsOrderyear,Count(DistinctCustomerid)AsNumcustsFromDBO.OrdersGroupYear(Orderdate))SelectCur.Orderyear,Cur.NumcustsAsCurnumcusts,PRV.NumcustsAsPrvnumcusts,Cur.Numcusts-PRV.NumcustsAsGrowthFromYearlycountAsCurLeft Outer JoinYearlycountAsPRVOnCur.Orderyear=PRV.Orderyear+1;
 

CTE instance 4 (modify data)

1. dynamically assemble the query results from the customer table into the customersdups table:

  If   object_id   (  'dbo. customersdups'  ) is not null   drop table  DBO .  customersdups ;  go  with  crosscustomers  as   (  select  1  as  C ,  C1 . *   from  DBO .  MERs  as  C1 ,  DBO .  MERs  as  C2 )   select   row_number   ()   over   (  order by  C )   as  keycol ,  customerid ,  companyName ,  contactname ,  contacttitle ,   address  ,  city ,  region ,  postalcode ,  country ,  phone ,  Fax  into  DBO .  customersdups  from  crosscustomers ; 

2. Use CTE to remove data. Only the records with the maximum keycol in the same mermerdups table are retained.

 
WithJustdupsAs(Select*FromDBO.CustomersdupsAsC1WhereKeycol<(SelectMax(Keycol)FromDBO.CustomersdupsAsC2WhereC2.Customerid=C1.Customerid))Delete fromJustdups;
 

CTE instance 5 (object container)

It provides encapsulation capabilities and facilitates componentized programming. The author reminds me that the CTE cannot be directly embedded, but can be embedded by encapsulating the CTE into an object container and querying the container data from an external CTE.

The author also explains that using ctes has no value in view and udfs.

An example is as follows:

Create ViewDBO.VyearcntAswithYearcntAs(SelectYear(Orderdate)AsOrderyear,Count(DistinctCustomerid)AsNumcustsFromDBO.OrdersGroupYear(Orderdate))Select*FromYearcnt;
 

CTE instance 6 (recursive ctes)

The author gave an example to describe the new content in sql2005, the recursion of ctes.

The employee information is returned based on the employee ID and contains information of all lower-level employees. The returned results contain the following fields: employeeid, reportsto, firstname, and lastname.

Here, the author gives an optimal indexing method:

Create unique indexIdx_mgr_emp_ifname_ilnameOnDBO.Employees(Reportsto,Employeeid)Include(Firstname,Lastname);
 

The author's explanation: This index uses a separate query (local scan) to obtain the direct subordinates of each manager. Here, include (fristname, lastname) overwrites the column.

TIPS: What include indexes?

Include index is a new function of sql2005. The columns with the include index do not affect the physical storage order of the index rows. They are mounted on the 'index row' as a pendant. The purpose of hanging these 'pendants 'is to scan an index to obtain the additional data.

Return to the author's example. The following is recursive.Code:

WithEmpscteAs(SelectEmployeeid,Reportsto,Firstname,LastnameFromDBO.EmployeesWhereEmployeeid=5Union all selectEMP.Employeeid,EMP.Reportsto,EMP.Firstname,EMP.LastnameFromEmpscteAsMgrJoinDBO.EmployeesAsEMPOnEMP.Reportsto=Mgr.Employeeid)Select*FromEmpscte;
 

Understanding: a recursive CTE contains at least two queries. The first query is similar to an anchor in the CTE body. This anchor only returns a valid table and serves as an anchor for recursion. As shown in the preceding example, the anchor returns only one row with an employee ID = 5. The Second 2nd queries are used as recursive members. This recursion ends when the query result of a subordinate member is null.

If you are worried that recursion will cause a permanent loop, you can use the following expression:

With cte_name as (cte_body) outer_query option (maxrecursion N );

The default N value is 100. When n = 0, there is no limit.