Simple application of SQL Server Common Expressions (CTE)

Project Data access now basically selects an ORM framework, which masks the underlying data access forms in an object-oriented manner, allowing developers to focus on business processing rather than interacting with the database, helping us to improve development efficiency; For example, some simple insert, UPDATE, We do not need to write the Insert Into...sql statement, but instead directly new an entity object, then db. Insert (entity), looks so refreshing, like EF, a more sophisticated ORM, support LINQ syntax to access the database, write it more cool, some people even think that developers can not write SQL statements ... But the reality will not make you work so easy, as a developer of the database this piece of learning is still very necessary, and do not say some flexibility and efficiency issues, the actual work with SQL is still very much, often in code code, suddenly came to the voice of the leader, that xxx, you hurriedly give me a statement, That who who, you hurriedly give me a copy of XXX data ... It's urgent.

Recently in code, the leader came to a sentence: Hey man, you give me statistics of all XXX products information, fast, there is urging .... Abstract here, as below, is probably to find out all the furniture products information, this classification table contains a tree structure, ParentID 0 is a classification of the root, it may have a lot of seed node/leaf node. What needs to be found here is a tree with furniture as its root.

　　

To test the SQL statement:

DECLARE @Product TABLE (ProductId int, parentid int, ProductName NVARCHAR) INSERT into @ProductVALUES (1, 0, ' furniture '), (2,0, ' Clothing '), (3,1, ' large furniture '), (4,1, ' small furniture '), (5,2, ' menswear '), (6,2, ' Ladies '), (7,3, ' bed '), (8,3, ' wardrobe '), (9,3, ' couch '), (10,4, ' computer desk '), (11,4, ' chair ') , (12,5, ' Jeans '), (13,5, ' shirts '), (14,6, ' skirts ')

This kind of demand is actually many, the experienced friend soon knows how to write, but the actual writing is also very simple. Know this is a tree structure, in the mind of the Appearance: self-link query, sub-query, temporary table, cursor, program write code recursion ... Common Expressions (CTE), ok! The syntax of the CTE is as follows:

with CTE name [target column]
As
(
< internal query defining a CTE >
)
< external query for a CTE query >

Specifically, a CTE is a table expression, another table expression is a derived table (subquery), and sometimes a CTE can be used to optimize our code, making our code easier and easier to read. And the CTE supports recursive queries, and the above requirements are written as:

;  With Cteas (SELECT * from @Product  WHERE ProductId = 1 UNION all   SELECT p.* from @Product P  INNER JOIN CTE T on P.parentid = T.productid) select*from CTE ORDER by ProductId

The recursive query of the CTE consists of two parts, the anchor member and the recursive member. As the above query, the select in the front of the union all is the anchor member, which is the initialization of the query, and the union all is subordinate to the recursive member, and we can return the last result set for the CTE each time we recursively query. For example, when initializing, the CTE result is ProductID 1, when the first recursion, the product ParentID 1 is found, that is 3,4, and the result set is returned with the last result set union ALL, and the CTE is 1,3,4 when recursive. And the end condition of the recursion is that the result of this query is an empty set, at which point the recursion ends and the final result sets are returned.

It is also necessary to say that the CTE is virtual, SQL Server will regenerate the query statements, direct access to the underlying object, so in some high performance requirements, or through the execution plan to determine whether the need for optimization, sometimes convenience is at the cost of performance.

Simple application of SQL Server Common Expressions (CTE)