Multi-level SQL processing, with query results in a tree structure

The disadvantage of this solution is that, if the data volume is large and there are many sub-categories, the number of sub-categories reaches 4 or above, this method occupies the database connection pool.
It has a great impact on performance.

If you use the SQL CTE for Recursive processing, you can query the results at one time, and the performance is very good.
Compared with program processing (when the data volume is large), temporary tables provide better performance and convenience.
Copy codeThe Code is as follows:
With area (
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 join area B on a. parentid = B. id
) Select * from area order by px, px2


Query results-subcategories under all categories and corresponding categories
Id title parentid
1 Guangdong 0
2 Guangzhou 1
3 Baiyun District 2
4 Shenzhen 1
5 Hunan 0
6 Changsha 5
7. Zhuzhou 5
Copy codeThe Code is as follows:
With area (
Select * from region where parentid = 1
Union all
Select a. * from region a join area B on a. parentid = B. id
) Select * from area

Query results-specify the category and subcategory under the corresponding category
Id title parentid
1 Guangdong 0
2 Guangzhou 1
3 Baiyun District 2


Performance analysis:
For a data table with 3500 region records, there are three levels: province, city, and county.
It takes 1 second to query, and it seems a little slow visually, but it does not affect
Classification with a small amount of data, absolutely no pressure on Use