Simple Application of SQL server public expressions and SQL

Simple Application of SQL server public expressions and SQL

I. Preface　　

Currently, An orm framework is basically used for project data access. It shields underlying data access forms in the form of object-oriented, allowing developers to focus more on business processing, instead of interacting with databases, it helps us improve development efficiency. For example, for some simple insert and update operations, we do not need to write insert... SQL statement, but directly new an object, and then db. insert (entity), looks so refreshing; a well-developed orm like EF supports the linq syntax to access the database, which makes it easier to write, some people even think that developers do not need to write SQL statements... but the reality won't make your work so easy, and it is necessary for developers to learn about the database, not to mention some flexibility and efficiency issues, in actual work, there are still a lot of places where SQL is used. When code is often used, the voice of the lead suddenly comes, that is so-and-so. You should give me a report quickly, who, who, and who, you need to give me a copy of XXX data... very urgent.

Ii. Use CTE to calculate the Tree Structure

Recently, when I was coding code, the leader came up with the following sentence: Hey man, you have to give me statistics on all the xxx products. It's coming soon .... Here, the abstract is as follows, which is probably to find information about all furniture products. This classification table contains a tree structure, and ParentId 0 is the root of a certain category, it may have many seed nodes/leaf nodes. Here, we need to find a tree based on furniture.

　

Test SQL statement:

DECLARE @ Product TABLE (ProductId INT, ParentId INT, ProductName NVARCHAR (64) insert into @ ProductVALUES (, 'furniture '), (, 'hangzhou, 'large furniture '), (, 'small furniture'), (, 'Men's wy'), (, 2, 'Women's wy'), (, 'bed '), (8, 3, 'wardrobe '), (9, 3, 'sofa'), (10, 4, 'computer desk '), (11, 4, 'chair'), (12, 5, 'jeans '), (, 'shirt '), (, 'skirt ')

Iii. Implementation

There are many practical requirements, and experienced friends will soon know how to write, and the actual writing is also very simple. I know this is a tree structure, and it appears in my mind: Self-linked queries, subqueries, temporary tables, cursors, code-writing recursion using programs... public expressions (CTE), OK! The syntax of CTE is as follows:

With cte name [target column] AS (<internal query of CTE defined>) <external query of CTE query>

Specifically, CTE is a table expression, and another table expression is a derived table (subquery). Sometimes we can use CTE to optimize our code to make our code simpler and easier to read. In addition, CTE supports recursive queries. The preceding requirement is written as follows:

;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

Iv. Analysis

The recursive query of CTE consists of positioning point Members and recursive members. In the preceding query, SELECT before union all is the positioning Point member, which is the initialization of the query. SELECT under union all is a recursive member. When we can perform recursive query, the last result set is returned for CTE each time. For example, during initialization, the cte result is ProductId 1. During the first recursion, the product with ParentId 1 is found, that is, 3, 4, union all with the previous result set returns the result set. After recursion, the cte is, 4, and the recursive end condition is that the result of this query is an empty set, the recursion ends and the final result set is returned.

In addition, the CTE is virtual, and the SQL server will generate a query statement for it to directly access the underlying object. Therefore, in some areas with high performance requirements, it is still necessary to determine whether optimization is needed through the execution plan. Sometimes the convenience is at the performance cost.

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, you can leave a message and share it with us!