SQL processing Multilevel classification, query results are tree-shaped structure

For multi-level classification of conventional processing methods, many programmers may use the program to read first-level classification records, and then through the first class classification loop read the following sub-classification

The disadvantage of this treatment is: if the amount of data, sub-classification a lot, reached level 4 above, this method of processing the extreme occupation database connection pool

Have a significant impact on performance.

If the CTE under the SQL is processed recursively, the results can be queried at once, and the performance is very good.

Better performance and more convenience for temporary tables than with programs (large data volumes)

With area as (
Select *,id px,cast (id as nvarchar (4000)) px2 from region where parentid=0
UNION ALL
Select A.*,b.px,b.px2+ltrim (a.region_id) from region A joins area B on a.parentid=b.id
) select * from area ORDER by px,px2

Can query the results-all categories and corresponding categories under the classification

ID Title ParentID

1 Guangdong Province 0

2 Guangzhou 1

3 Baiyun District 2

4 Shenzhen 1

5 Hunan Province 0

6 Changsha 5

7 Zhuzhou 5

With area as (
SELECT * from region where parentid=1
UNION ALL
Select A.* from Region A joins area B on a.parentid=b.id
) SELECT * FROM area

Can query the results--Specify classification and corresponding classification under the classification

ID Title ParentID

1 Guangdong Province 0

2 Guangzhou 1

3 Baiyun District 2

Performance Analysis:

For a data table of 3,500 regional records, including provincial, municipal, and county level 3

The query takes 1 seconds, visually feels a little bit slow, but does not affect

Classification with a small amount of data, using absolutely no pressure

SQL processing Multilevel classification, query results are tree-shaped structure