Simple application of SQL Server common expressions _ database other

First, the preface　　

Now do project data access will basically choose an ORM framework, it in object-oriented form to screen the underlying data access form, so that developers more focused on business processing, rather than the interaction with the database to help us improve development efficiency; For example, some simple insert, UPDATE, We do not need to write the Insert Into...sql statement, but directly new entity object, then db. Insert (entity), looks so refreshing, like the EF Perfect orm, supporting LINQ syntax to access the database, it is more fun to write, some people even think that developers can not write SQL statements ... But the reality will not let you work so easily, as a developer of the database this piece of learning is still very necessary; and do not say some flexibility and efficiency problems, the actual use of SQL in the place is still very much, often in code codes, suddenly came the voice of the leader, that So-and-so, you hurriedly give me a report, That who who, you hurriedly give me a copy of XXX data ... It's urgent.

Ii. using a CTE for statistical tree-shaped structures

Recently in the code, the leader came to a: Hey man, you give me statistics of all the XXX product information, to fast, there in the rush .... Here the abstract, as follows, presumably is to find all the furniture products information, this classification table contains a tree structure, ParentID 0 is the root of some sort, it may have a lot of seed node/leaf nodes below. What needs to be found here is a tree rooted in furniture.

　

To test the SQL statement:

DECLARE @Product TABLE
(ProductId int,
 parentid int,
 ProductName NVARCHAR ())
INSERT into @Product
VALUES
(1, 0, ' furniture '), (
2,0, ' costume '), (
3,1, ' large furniture '),
(4,1, ' small furniture '), (
5,2, ' menswear '
), (6,2, ' Ladies '), (7,3, ' " Bed '),
(8,3, ' wardrobe '), (
9,3, ' sofa '), (
10,4, ' computer table '), (
11,4, ' chair
'), (12,5, ' Jeans '), (13,5, ' shirts ') ),
(14,6, ' skirts ')

Third, realize

This kind of demand actually many, the experienced friend soon knew how to write, but the actual writing is also very simple. Know that this is a tree structure, in the mind appears: self-linked query, subquery, temporary table, cursor, write code in the program recursion ... Public expression (CTE), ok! The syntax for a CTE is as follows:

with CTE name [target column]
as
(
< define CTE internal query >
)
< external query for CTE (s) >

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

; With the CTE as
(SELECT * @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
   

Iv. Analysis

The recursive query for a CTE consists of two parts, anchor members and recursive members. As the above query, the select in the Union all is the anchor member, which is the initialization of the query; the union all belongs to the recursive member, and we can recursively return the last result set for the CTE each time. For example, when initialized, the CTE result is ProductID 1, and the first recursion is to find the product ParentID 1, which is 3,4, and returns the result set with the last result set union ALL, and then the CTE is 1,3,4. , and the recursive end condition is that the result of this query is the empty set, at which point the recursion ends, and the final result sets are returned.

In addition, the CTE is virtual, and SQL Server regenerates the query statement for it, accessing the underlying object directly, so in some places where performance requirements are high, it is necessary to determine whether or not to optimize by executing the plan, sometimes at the expense of performance.

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring some help, if there are questions you can message exchange, but also hope that a lot of support cloud Habitat community!