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

Sql server how to use public expressions, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

I. Preface

Now when we do project data access, we basically choose an orm framework, which shields the underlying data access form in an object-oriented form, allowing developers to focus more on business processing rather than interaction with the database, helping us to improve development efficiency. For example, some simple insert, update, we do not need to write insert into...sql statements, but directly new an entity object, and then db.Insert (entity), looks so refreshing; a relatively complete orm like EF, which supports linq syntax to access the database, is even more comfortable to write, and some people even think that developers can write sql statements without having to write sql statements. But the reality will not let you work so easily, as a developer to learn this part of the database is still very necessary; not to mention some flexibility and efficiency issues, the actual work of the use of sql is still very many, often in the code, suddenly heard the leader's voice, that so-and-so, you hurry to give me a report, that who, you quickly give me a XXX data. It's urgent.

Second, use CTE to calculate the tree structure

Recently in the code, the leader said: Hey man, you give me the statistics of all xxx products, fast, there is a rush. Here is an abstraction, as follows, probably to find out the information of all furniture products, this classification table contains a tree structure, ParentId 0 is the root of a certain classification, and there may be many seed / leaf nodes under it. What we are looking for here is actually a tree with furniture as its root.

Test the sql statement:

DECLARE @ Product TABLE (ProductId INT, ParentId INT, ProductName NVARCHAR (64)) INSERT INTO @ ProductVALUES (1pr 0pje 'furniture'), (2pje 'costume'), ('large' furniture'), (''small furniture'), ('menswear'), ('ladies'), ('bed'), ('wardrobe'), ('sofa'), ('computer desk'), ('chairs'), ('chairs') 'jeans'), (13men5 pencils' shirts'), (14pence6 'skirts')

III. Realization

This kind of demand is actually a lot, experienced friends will soon know how to write, and the actual writing method is also very simple. Knowing that this is a tree structure, it comes to mind: self-linked queries, subqueries, temporary tables, cursors, code recursion with programs. Common expression (CTE), OK! The syntax of CTE is as follows:

WITH CTE name [destination column] AS ()

Specifically, CTE belongs to table expression, and another kind of table expression is derived table (subquery). Sometimes using CTE can optimize our code and make our code simpler and easier to read. And CTE supports recursive queries. The above requirements are 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 mainly consists of two parts, anchor member and recursive member. As in the above query, the SELECT in front of UNION ALL is the anchor member, which is the initialization of the query; the one under UNION ALL is a recursive member, and we can return the last result set for CTE each time when we recursively query. For example, when initializing, the cte result is ProductId 1. On the first recursion, you will find a product with a ParentId of 1, that is, 3Magin4, and this result set will be returned with the previous result set UNION ALL. When recursive, cte will return the result set. The end condition of recursion is that the query result is an empty set, and the recursion will end and return the final result set.

In addition, it should be said that CTE is virtual, and sql server will regenerate the query statement for it and directly access the underlying objects; so in some places with high performance requirements, it is necessary to determine whether optimization is needed by executing the plan, and sometimes convenience is at the cost of performance.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.