The disadvantage of this treatment is: if the amount of data, a lot of subcategories, up to 4 level, this method to handle extreme occupancy database connection pool
has a significant impact on performance.
If you use a CTE with the following SQL, you can query the results in a single pass, and the performance is good.
The performance of the temporary table is better and more convenient than the program processing (the data is very large)
Copy Code code as follows:
With the 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 join area B on a.parentid=b.id
) select * from area ORDER by px,px2
Can query the results--all categories and corresponding categories
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
Copy Code code as follows:
With the area as (
SELECT * from region where parentid=1
UNION ALL
Select A.* from region A join area B on a.parentid=b.id
) SELECT * FROM area
Can query the results--Specify the classification and the corresponding classification
ID Title ParentID
1 Guangdong Province 0
2 Guangzhou 1
3 Baiyun District 2
Performance Analysis:
For a 3,500-region record data sheet, which has a province, city, county level 3
The query takes 1 seconds, visually it feels a little bit slower, but it doesn't affect
A small amount of data, using absolutely no pressure
