A common table expression (CTE) can be considered a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
A CTE is similar to a derived table in that it is not stored as an object and is valid only for the duration of the query.
Unlike a derived table, a CTE can be referenced and can be referenced multiple times in the same query.
A CTE can be used to:
1. Create a recursive query (I personally think the best place for a CTE)
2. Referencing the generated table multiple times in the same statement
CTE Benefits:
Using a CTE can gain the benefits of improving readability and easy maintenance of complex queries.
Queries can be divided into individual blocks, simple blocks, and logical building blocks. These simple blocks can then be used to generate more complex temporary CTE until the final result set is generated.
The range that a CTE can use:
You can define a CTE in a user-defined routine, such as a function, stored procedure, trigger, or view.
Let's look at a simple CTE example:
Save the largest ID record in the test table in Test_cte, and then call the salary
Copy Code code as follows:
With Test_cte (id,salary)
As
(
Select ID, max (Salary)
From Test
GROUP BY ID
)
SELECT * FROM Test_cte
As can be seen from the above examples:
A CTE consists of an expression name that represents a CTE, an optional column list, and a query that defines CET.
Once a CTE is defined, it can be referenced in a SELECT, INSERT, UPDATE, or DELETE statement, just like a reference table or view.
Simply put, a CTE can replace the function of temporary tables and table variables.
I personally think that the best place for a CTE is to create a recursive query, which is shown below:
One of the existing data structures is as follows:
The data is stored in table Co_itemnameset, and the table structure and some of the data are as follows:
ItemId Parentitemid ItemName
2 0 Management fee
3 0 Sales costs
4 0 Financial costs
5 0 production costs
35 5 Materials
36 5 Artificial
37 5 Manufacturing costs
38 35 Raw Materials
39 35 Main Materials
40 35 Auxiliary Materials
41 36 Wages
42 36 Welfare
43 36 Bonus
Now the requirement is: I want to query itemid=2, that is, the management fee and all the nodes in its subordinate information
A CTE can be very simple to achieve the required data
In order to embody the convenience of CTE, I purposely also wrote a sql2000 version of the solution, first see how sql2000 to solve this problem
Copy Code code as follows:
--sql2000 version
DECLARE @i INT
SELECT @i=2;
/*
Use a temporary table as a stack to track all items that are being processed (started but not yet closed).
Once an item has been processed, it will be removed from the stack.
When a new project is found, the items are added to the stack.
*/
CREATE TABLE #tem (
[ItemId] [INT] Not NULL,
[Level] Int
);
/*
Store results
*/
CREATE TABLE #list (
[ItemId] [INT] Not NULL,
[Parentitemid] [INT] Not NULL DEFAULT ((0)),
[ItemName] [nvarchar] (MB) not NULL DEFAULT ('),
[Level] Int
);
INSERT into #tem ([Itemid],[level])
SELECT ItemId, 1
From Co_itemnameset
WHERE itemid=@i
INSERT into #list ([Itemid],[parentitemid],[itemname],[level])
SELECT ItemId, Parentitemid, ItemName, 1
From Co_itemnameset
WHERE itemid=@i
DECLARE @level INT
SELECT @level =1
DECLARE @current INT
SELECT @current =0
/*
When the @level is greater than 0 o'clock, perform the following steps:
1. If there are items on the stack of the current level (@level), select one and call it a @current.
2. Remove the item from the stack to avoid repeating it, and then add all of its subprojects to the next level of the stack (@level + 1).
3. If there are subprojects (if @ @ROWCOUNT > 0), they are processed at a lower level (@level = @level + 1), otherwise they continue to be processed at the current level.
4. Finally, if there are no items to be processed at the current level of the stack, go back to the previous level and see if the item is pending at the level (@level = @level-1). When there is no further level, then complete.
*/
while (@level >0)
BEGIN
SELECT @current =itemid
From #tem
WHERE [level]= @level
IF @ @ROWCOUNT >0
BEGIN
--Remove the item from the stack so that it is not processed repeatedly
DELETE from #tem
WHERE [level]= @level and Itemid= @current
--Add all of its subprojects to the next level of the stack (@level + 1).
INSERT into #tem ([Itemid],[level])
SELECT [ItemId], @level +1
From Co_itemnameset
WHERE parentitemid= @current
--Add all of its subprojects
INSERT into #list ([Itemid],[parentitemid],[itemname],[level])
SELECT [Itemid],[parentitemid],[itemname], @level +1
From Co_itemnameset
WHERE parentitemid= @current
IF @ @rowcount >0
BEGIN
SELECT @level = @level +1
End
End
ELSE
BEGIN
SELECT @level = @level-1
End
End
--Show results
SELECT * FROM #list
DROP TABLE #tem
DROP TABLE #list
Go
The results are as follows:
ItemId Parentitemid ItemName Level
2 0 Management fee 1
52 2 car Cost 2
55 2 Recruitment Fee 2
56 2 Sewage Charge 2
53 52 Fuel 3
54 52 Tires 3
We see sql2000 solve this problem more trouble, to achieve this need to write code more, more complex
Well now, in sql2005, the recursive characteristics of a CTE can be achieved in 2 steps.
With the same result, Sql2005 's CTE code is much simpler. This is the charm of a CTE to support recursive queries.
Take a look at the following code:
Copy Code code as follows:
--sql2005 version
DECLARE @i INT
SELECT @i=2;
with Co_itemnameset_cte (ItemId, Parentitemid, Itemname,level)
as
(
SELECT ItemId, Parentitemid, Item Name, 1 as [level]
from Co_itemnameset
WHERE itemid=@i
UNION all
SELECT c.itemid, C.parentitemid, C.itemname, [level] + 1
co_itemnameset c INNER JOIN Co_itemnameset_cte CT
on c.parentitemid=ct. ItemId
)
SELECT * from Co_itemnameset_cte
Go
