Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use with as in SQL Server. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

I. the meaning of WITH AS

The WITH AS phrase, also known as the subquery section (subquery factoring), allows you to do a lot of 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 the UNION ALL as part of providing data. It is especially useful for UNION ALL. Because each part of the UNION ALL may be the same, but if each part is executed once, the cost is too high, so you can use the WITH AS phrase, just 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 an TEMP table, but not if it is called only once. The prompt materialize forces the data in the WITH AS phrase to be placed in a global temporary table. Many queries can be improved in this way.

Two. usage

Let's first look at the following nested query statement:

Select * from person.StateProvince where CountryRegionCode in (select CountryRegionCode from person.CountryRegion where Name like'C%')

The above query statement uses a subquery. Although this SQL statement is not complex, if there are too many levels of nesting, it will make the SQL statement very difficult to read and maintain. Therefore, you can also use table variables to solve this problem, as shown in the SQL statement:

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 approach, it puts the subquery in the table variable @ t, which makes the SQL statement easier to maintain, but creates another problem, which is a loss of performance. Because the table variables actually use temporary tables, which adds additional I _ map O overhead, the table variable approach is not very suitable for situations with large amounts of data and frequent queries. To this end, another solution is provided in SQL Server 2005, which is the common table expression (CTE). Using CTE, you can make SQL statements maintainable, and CTE is much more efficient than table variables.

Here is the syntax of CTE:

[WITH [, n]]: = expression_name [(column_name [, n])] AS (CTE_query_definition)

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

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 in use to table variables, except that SQL Server 2005 differs in the way it handles common table expressions.

The following points should be noted when using CTE:

1. CTE must be followed directly by SQL statements using CTE (such as select, insert, update, etc.), otherwise, CTE will become invalid. The following SQL statement will not work properly with CTE:

Withcr as (select CountryRegionCode from person.CountryRegion where Name like'C%') select * from person.CountryRegion-this SQL statement should be removed-SQL statements using CTE should be immediately following the relevant CTE-select * from person.StateProvince where CountryRegionCode in (select * from cr)

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

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 CTE is the same as that of a data table or view, the SQL statement immediately following that CTE still uses CTE, and of course, the subsequent SQL statement uses the data table or view, as shown in the following SQL statement:

-- table1 is an actual table

Withtable1 as (select * from persons where age < 30) select * from table1-- using a common table expression select * from table1 named table1-- uses a data table named table1

4. The CTE can refer to itself or to a predefined CTE in the same WITH clause. Forward references are not allowed.

5. You cannot use the following clause 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 you use CTE in a statement that is part of a batch, the statement that precedes it must end with a semicolon, as shown in the following SQL:

Declare @ s nvarchar (3) set @ s ='C% from t_tree;-- semicolon witht_tree as (select CountryRegionCode from person.CountryRegion where Name like @ s) select * from person.StateProvince where CountryRegionCode in (select * from t_tree) must be added

In addition to simplifying nested SQL statements, CTE can also make recursive calls, which will be covered in the next article.

First, look at the following data table (t_tree):

The figure above shows the data in a table with three fields: id, node_name, and parent_id. In fact, a tree structure is stored in this table, which is divided into three layers: province, city and district. Where id represents the id number of the current province, city, or district, node_name represents the name, and parent_id represents the id of the parent node of the node. There is now a need to query all cities and districts under a province (the query results include provinces). If you only use SQL statements, you need to use techniques such as cursors, temporary tables, and so on. But it can also be implemented using CTE in SQL Server2005.

From the point of view of this requirement, it is a recursive call, that is to say, first find out the record of the province that meets the price adjustment. In this example, check the record of "Liaoning Province", as follows:

Id node_name parent_id

1 Liaoning Province 0

Then check all records with a parent_id field value of 1, as follows:

Id node_name parent_id

2 Shenyang 1

3 Dalian 1

Finally, check the records with a parent_id field value of 2 or 3, as follows:

Id node_name parent_id

4 Great East 2

Shenhe District 2

6 Tiexi District 2

Putting the above three results together is the final result set.

The above query process can also be understood according to the recursive process, that is, first check the record of the designated province (Liaoning Province), after getting this record, you will have the corresponding id value, and then enter the recursive process, as shown in the following figure.

As you can see from the above, the process of recursion is the process of merging query result sets using union all, which is equivalent to the following recursive formula:

Resultset (n) = resultset (nmur1) union all current_resultset

Where resultset (n) represents the final result set, resultset (n-1) represents the penultimate result set, current_resultset represents the currently found result set, and the record set of "Liaoning Province" at the beginning is equivalent to the recursive initial condition. The end condition of recursion is that current_resultset is empty. Here is the pseudo code for this recursive process:

Public resultset getResultSet (resultset) {if (resultset is null) {current_resultset = first result set (including provincial recordset) Save the id of the result set in the collection getResultSet (current_resultset)} current_resultset = find the current result set based on the id value in the id collection if (current_result is null) return resultset save the id of the current result set in the collection return getResultSet (resultset union all current_ Resultset)} / / get the final result set resultset = getResultSet (null)

From the above process, we can see that this recursive process is relatively complex to implement, but CTE provides us with a simple syntax to simplify the process. The CTE syntax for implementing recursion is as follows:

[WITH [, n]]:: = expression_name [(column_name [, n])] AS (CTE_query_definition1-anchor point member (that is, initial value or first result set) union all CTE_query_definition2-recursive member)

Sql statement

Withdistrict as (--gets the first result set and updates the final result set select * from t_tree where node_name= N' Liaoning 'union all-- the following select statement first queries the value of the parent_id-- field based on the id value obtained from the previous query result set, and then district changes the current query result set and continues to execute the following select statement-- if the result set is not null Then merge with the final query result, and update the final query result with the merged result. Otherwise, the execution will stop. The final result set of district is the final result set. Select a.* from t_tree a, district b where a.parent_id = b.id) select * from districtwithdistrict as (select * from t_tree where node_name= N' Liaoning Province 'union all select a.* from t_tree a, district b where a.parent_id = b.id), district1 as (select a.* from district a where a.id in (select parent_id from district)) select * from district1

Note: only "Liaoning Province" and "Shenyang" have the following child nodes.

The following points should be noted when defining and using recursive CTE:

1. A recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. Multiple anchor point members and recursive members can be defined; however, all anchor point member query definitions must be placed before the first recursive member definition. All CTE query definitions are anchor members, except when they reference the CTE itself. two。 Anchor members must be used in conjunction with one of the following collection operators: UNION ALL, UNION, INTERSECT, or EXCEPT. Only the UNION ALL collection operator can be used between the last anchor member and the first recursive member, and when combining multiple recursive members. 3. The number of columns in anchor members and recursive members must be the same. 4. The data type of the column in the recursive member must be the same as that of the corresponding column in the anchor member. 5. The FROM clause of a recursive member can refer to CTE expression_name only once. 6. The following items are not allowed in the CTE_query_definition of a recursive member:

(1) SELECT DISTINCT (2) GROUP BY (3) HAVING (4) Scalar aggregation (5) TOP (6) LEFT, RIGHT, OUTER JOIN (INNER JOIN allowed) (7) subquery (8) hint to apply recursive references to CTE in CTE_query_definition.

7. All columns returned by a recursive SELECT can be empty regardless of the nullness of the columns returned by the participating CTE statement. 8. If the recursive CTE is not combined correctly, it may result in an infinite loop. For example, if a recursive member query definition returns the same value for both parent and child columns, it will cause an infinite loop. You can use MAXRECURSION hints and a value between 0 and 32767 in the OPTION clause of an INSERT, UPDATE, DELETE, or SELECT statement to limit the number of recursive levels allowed by a particular statement to prevent infinite loops. This allows you to control the execution of statements before solving the code problem that generates the loop. The server-wide default value is 100. If you specify 0, there is no limit. Only one MAXRECURSION value can be specified per statement. 9. You cannot update data with views that contain recursive common table expressions. 10. You can use CTE to define cursors on a query. Recursive CTE allows only fast forward-only cursors and static (snapshot) cursors. If a different cursor type is specified in the recursive CTE, the type is converted to a static cursor type. 11. You can reference a table in a remote server in CTE. If the remote server is referenced in a recursive member of the CTE, a spool is created for each remote table so that the tables can be accessed repeatedly locally.

Here are some additions, many reference values

The WITH AS phrase, also known as the subquery section (subquery factoring), allows you to do a lot of things, defining a SQL fragment that will be used by the entire SQL statement.

As part of providing data.

Code example:

With temp as (select ID, Type_Name, Type_ID from T_Base_GoodsType as t where t.Shop_ID = @ shop_id and Type_ID = @ Goods_TypeID union all select t1.ID, t1.Type_Name, t1.Type_ID from T_Base_GoodsType as T1 inner join temp on t1.ParentType_ID = temp.Type_ID where t1.Shop_ID = @ shop_id) select * from (select Stock_Amount, S.StockWarn_Amount, S.All_Amount G.Goods_ID, G.Goods_Name, G.Goods_Unit ROW_NUMBER () over (order by Stock_Amount desc) as rowid from T_IM_StockInfo as S inner join T_Base_GoodsInfo AS G on S.Goods_ID = G.Goods_ID inner join temp on temp.Type_ID = G.Goods_TypeID where S.Shop_ID = @ shop_id AND G.Shop_ID = @ shop_id and G.Goods_TypeID = temp.Type_ID group by S.Stock_Amount S.All_Amount, G.Goods_ID, G.Goods_Name, G.Goods_Unit, S.StockWarn_Amount HAVING SUM (S.Stock_Amount) < S.StockWarn_Amount) m WHERE rowid between @ pageindex and @ pagesize

Sql loop (the WITH AS phrase is also called the subquery part)

-- Table structure SELECT id,position,Parentid FROM op_client_sales_structure WITH TEST_CTE AS (SELECT id,position,Parentid,Cast (Parentid AS NVARCHAR (4000)) AS PATH FROM op_client_sales_structure team WHERE Parentid! =-1 UNION ALL SELECT a.position.Parentid, CTE.PATH+' '+ Cast (a.Parentid AS NVARCHAR (4000) AS PATH FROM op_client_sales_structure an INNER JOIN TEST_CTE CTE ON a.id=CTE.Parentid) SELECT * FROM TEST_CTE WHERE Parentid= (SELECT id FROM op_client_sales_structure WHERE Parentid=-1)-- limit the number of recursions OPTION (MAXRECURSION 10) so much for sharing on how to use with as in SQL Server I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.