1, CTE characteristics
Starting from SQL Server 2005, syntax support for CTE(Common Table Expression) is provided.
CTE is a temporarily named result set defined in SELECT, INSERT, UPDATE or DELETE statements. At the same time, CTE can also be used in the definition of views.
A reference to itself can be included in the CTE, so this expression is also called recursive CTE.

1. Advantages of CTE
The function provided by common table expression is similar to that of view, but it does not save SQL statements in our database like view.
Advantages of using CTE officially given by Microsoft:

1. You can write a recursive query.
2. You want to use a view like function, but you don't want to save the definition of the query SQL statement to the database.
3. To reference an SQL statement that returns data multiple times, you only need to define it once.
Using CTE, complex SQL statements can be logically divided into several simple and independent common table expressions (CTEs). The biggest advantage is that it can improve the readability and maintainability of SQL statements.

In summary, CTE can be mainly used to recurse tree structure and simplify SQL statements to increase readability and maintainability.

2. Usage scenario of CTE
Due to business needs, we often write some complex SQL statements, which may contain many joins or sub queries. It is a headache to maintain and clarify the JOIN relationship of N multiple tables. Using CTE can make it easier to maintain and understand complex SQL statements.
Subqueries are generally used in development: they need to be nested from a complex subquery or even multi-level subquery. In this case, in the whole SQL statement, whether you write the SQL statement directly or wrap this SQL statement into a sub query and then access it with an alias, when the business requirements become more and more complex, you may need to modify this long and complex SQL statement segment at any time, and maintaining this complex and poorly readable SQL statement is a nightmare.
With CTE, we can use CTE to define an SQL statement and define an alias for the result set returned after the SQL statement is executed. Then, we can use this alias to reference these pre executed returned data sets, just like using an ordinary table.

3. Syntax of CTE
A common table expression consists of three main parts:

1.CET name (after WITH and before column name).
2. Column name column (optional).
3. Body of CET query statement (contents enclosed after AS).

2, SQL recursive tree structure Demo

1. Create a table

CREATE TABLE category_Organization
(
	[ID] INT NOT NULL IDENTITY(1,1),--Primary key
    [GUID] INT NOT NULL,  --business ID(Unique identification)
	[OrgCode] NVARCHAR(50) NULL, --Organization number
	[OrgName] NVARCHAR(50) NULL, --Organization name
	[OrgPGUID] NVARCHAR(50) NULL,--Superior organization
	[ilevel] int null--level
)
GO
 INSERT category_Organization VALUES(1,'a1','China Telecom','0',1)
 INSERT category_Organization VALUES(2,'b1','China Telecom Shaanxi Branch','1',2)
 INSERT category_Organization VALUES(3,'c1','China Telecom Xi'an Branch','2',3)
 INSERT category_Organization VALUES(4,'d1','China Telecom high tech business hall','3',4)
 INSERT category_Organization VALUES(5,'e1','Hi tech business customer service department','4',5)
  INSERT category_Organization VALUES(6,'a2','China Mobile','0',1)
 INSERT category_Organization VALUES(7,'b2','China Mobile Shaanxi Branch','6',2)
 INSERT category_Organization VALUES(8,'c2','China Mobile Xi'an Branch','7',3)
 INSERT category_Organization VALUES(9,'d2','China Mobile hi tech business hall','8',4)
 INSERT category_Organization VALUES(10,'e2','Hi tech business customer service department','9',5)
SELECT *FROM category_Organization

2. Use SQL recursion - find all child nodes according to the parent node
Put into stored procedure

CREATE PROCEDURE Child_ById
@id int --Unique identification
AS
BEGIN
with cte as
(
select category_Organization.GUID,OrgName,OrgPGUID from category_Organization where category_Organization.GUID=@id
union all
select a.GUID,a.OrgName,a.OrgPGUID from category_Organization a join cte b on a.OrgPGUID = b.GUID
)
select * from cte order by GUID asc
end
--EXEC Child_ById 1
 


3.use SQL recursion-Find all parent nodes by child nodes
CREATE PROC Parent_ById
@id int --Unique identification
AS
BEGIN
WITH CTE(GUID,OrgPGUID,OrgName,ilevel)
 AS
(
	SELECT GUID,OrgPGUID,OrgName,ilevel FROM category_Organization WHERE GUID=@id UNION ALL
	SELECT B.GUID,B.OrgPGUID,B.OrgName,B.ilevel FROM CTE A,category_Organization B WHERE A.OrgPGUID=B.GUID
)
SELECT GUID,OrgPGUID,OrgName,ilevel FROM CTE order by ilevel asc
end
--exec Parent_ById 10
 

--------
Copyright notice: This is the original article of CSDN blogger "Dust_SongYunfei", which follows the CC 4.0 BY-SA copyright agreement. Please attach the original source link and this notice for reprint.
Original link: https://blog.csdn.net/dust__/article/details/106436075