About recursive queries that use a CTE (common table expression)----SQL Server 2005 and above
A common table expression (CTE) has an important advantage of being able to reference itself to create a recursive CTE. A recursive CTE is a common table expression that repeats an initial CTE to return a subset of data until the full result set is obtained.
When a query references a recursive CTE, it is called a recursive query. Recursive queries are typically used to return hierarchical data, such as displaying an employee or BOM proposal in an organization chart where the parent product has one or more components, and those components may have subcomponents, or components of other parent products.
A recursive CTE can greatly simplify the code that is required to run a recursive query in a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. In earlier versions of SQL Server, recursive queries often required the use of temporary tables, cursors, and logic to control the flow of recursive steps.
The basic grammatical structure of a CTE is as follows:
Copy Code code as follows:
With Expression_name [(column_name [,... n])]
As
(cte_query_definition)
-The column Name list is optional only if a different name is provided for all result columns in the query definition.
-the statement that runs the CTE is:
SELECT <column_list> from Expression_name;
The following points should be noted when using a CTE:
The CTE must be followed directly by an SQL statement that uses a CTE (such as SELECT, Insert, UPDATE, and so on), otherwise the CTE will fail. If the following SQL statement will not work with a CTE:
Copy Code code as follows:
With
CR as
（
SELECT * FROM table name where condition
）
--select * from person. CountryRegion--If you add this sentence, the CR will be the error.
SELECT * FROM CR
2. A CTE may also be followed by a CTE, but only one with, separated by commas (,) between multiple CTE, as shown in the following SQL statement:
Copy Code code as follows:
With
Cte1 as
(
SELECT * FROM table1 where name like ' Test% '
),
Cte2 as
(
SELECT * from table2 where ID > 20
),
Cte3 as
(
SELECT * FROM Table3 where price < 100
)
Select A.* from Cte1 A, cte2 B, cte3 c where a.id = b.id and a.id = C.id
3. If the expression name of a CTE is the same as a data table or view, the SQL statement immediately following the CTE is still using a CTE, and of course, the following SQL statement uses a datasheet or view.
4. A CTE can refer to itself, or it can reference a CTE that is predefined in the same with clause.
5. The following clauses cannot be used in CTE_query_definition:
Copy Code code as follows:
COMPUTE or COMPUTE by
Order by (unless a TOP clause is specified)
Into
OPTION clause with query hints
For XML
For BROWSE
6. If a CTE is used in a statement that is part of a batch, the statement before it must end with a semicolon, as shown in the following SQL:
Copy Code code as follows:
declare @s nvarchar (3)
Set @s = ' Test% '; --You must add a semicolon
With
T_tree as
(
SELECT * FROM table where field like @s
)
SELECT * FROM T_tree
------------------------------------Operation------------------------------------
The above may be a bit verbose with AS, and here's the point:
The old rules of the first table (Co_itemnameset):
Copy Code code as follows:
CREATE TABLE [dbo]. [Co_itemnameset] (
[ItemId] [INT] Null
[Parentitemid] [INT] Null
[ItemName] [NCHAR] (a) COLLATE chinese_prc_ci_as NULL
) on [PRIMARY]
Insert data:
Copy Code code as follows:
--inserting data into a table
INSERT INTO dbo. Co_itemnameset values (2,0, ' management fees ')
INSERT INTO dbo. Co_itemnameset values (3,0, ' sales costs ')
INSERT INTO dbo. Co_itemnameset values (4,0, ' financial expenses ')
INSERT INTO dbo. Co_itemnameset values (5,0, ' production costs ')
INSERT INTO dbo. Co_itemnameset values (35,5, ' materials ')
INSERT INTO dbo. Co_itemnameset values (36,5, ' artificial ')
INSERT INTO dbo. Co_itemnameset values (37,5, ' manufacturing costs ')
INSERT INTO dbo. Co_itemnameset values (38,35, ' raw materials ')
INSERT INTO dbo. Co_itemnameset values (39,35, ' main material ')
INSERT INTO dbo. Co_itemnameset values (40,35, ' auxiliary materials ')
INSERT INTO dbo. Co_itemnameset values (41,36, ' wages ')
INSERT INTO dbo. Co_itemnameset values (42,36, ' benefits ')
INSERT INTO dbo. Co_itemnameset values (43,2, ' Administrative expenses subkeys ')
INSERT INTO dbo. Co_itemnameset values (113,43, ' Children of administrative fee subkeys ')
Query inserted data:
Copy Code code as follows:
--Querying data
SELECT * FROM Co_itemnameset
Result diagram:
The demand for the question is: query itemid=2 and sub nodes, that is, the management costs and all the nodes of its subordinate information
Operation 1: Take a look at the following SQL statements without a CTE recursive operation (need to build two tables for data storage and judgment, very troublesome):
Copy Code code as follows:
DECLARE @i int
Select @i=2;
CREATE TABLE #tem (
[ItemId] [INT] Not NULL,
[Level] Int
);
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
while (@level >0)
Begin
Select @current =itemid
From #tem
where [level]= @level
If @ @ROWCOUNT >0
Begin
Delete from #tem
where [level]= @level and Itemid= @current
INSERT into #tem ([Itemid],[level])
Select [ItemId], @level +1
From Co_itemnameset
where parentitemid= @current
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
SELECT * FROM #list
drop table #tem
drop TABLE #list
Result diagram:
Action 2: The SQL statements that are recursively manipulated with a CTE are as follows:
Copy Code code as follows:
DECLARE @i INT
SELECT @i=2;
With Co_itemnameset_cte (Itemid,parentitemid,itemname,[level])
As
(
SELECT itemid,parentitemid,itemname,1 as [level]
From Co_itemnameset
WHERE itemid=@i
UNION All
SELECT C.itemid,c.parentitemid,c.itemname,[level] + 1
From Co_itemnameset c INNER JOIN Co_itemnameset_cte CT
On C.PARENTITEMID=CT. ItemId
)
SELECT * from Co_itemnameset_cte
Result diagram:
-----------------------------Analysis (view MSDN Analysis)----------------------------
The main analysis is to use the recursive operation of CTE:
A recursive CTE consists of the following three elements:
The call to the routine.
The first invocation of a recursive CTE consists of one or more cte_query_definitions joined by the Union All, Union, EXCEPT, or INTERSECT operators. Because these query definitions form the base result set of the CTE structure, they are referred to as "anchor members."
Cte_query_definitions are treated as anchor members unless they refer to the CTE itself. All anchor member query definitions must be placed before the first recursive member definition, and the UNION all operator must be used to join the last anchor member and the first recursive member.
Recursive invocation of the routine.
A recursive call consists of one or more cte_query_definitions (that is, the statement block in as) that is joined by the UNION all operator referencing the CTE itself. These query definitions are referred to as "recursive members."
Terminate the check.
The termination check is implicit, and recursion stops when the row is not returned in the previous call.
The recursive CTE structure must contain at least one anchor member and one recursive member. The following pseudocode shows the components of a simple recursive CTE that contains an anchor member and a recursive member.
Copy Code code as follows:
With Cte_name (column_name [,... n])
As
(
CTE_query_definition--Locator point member
UNION All
CTE_query_definition--Recursive member.
)
now let's take a look at the recursive execution process:
Splits a CTE expression into an anchor member and a recursive member.
Run the anchor member to create the first call or datum result set (T0).
Run the recursive member, the Ti as input, the ti+1 as output.
Repeat step 3 until the empty set is returned.
Returns the result set. This is the result of the execution of UNION all for T0 to Tn.
