Oracle hierarchical query (administrative division level 3 cascade)

Prerequisites:
Database Table DM_xzqh example (partial ): Copy codeThe Code is as follows: DM MC
230000 Heilongjiang Province
230100 Harbin
230101 municipal jurisdiction
230102 daoli District
232700 Daxinganling
230103 Nangang District
230104 road External Zone
230108 normal housing area
230109 North songbei District
230110 xiangfang District
230111 Hulan District
230112 Acheng District
230123 Yilan County
238000 agricultural reclamation Bureau
Founder County, 230124
230125 binxian County
Bayan County 230126
230127 Mulan county
230128 Tonghe County
230129 yanshou County
230182 double cities
Shangzhi city, 230183

The preceding administrative divisions are divided into three levels by code: province (the last four digits are 0)/City (the last two digits are 0)/County, and their levels are marked separately, in this way, it is easy to query based on different levels later.

First, expand a new sjbm line based on the preceding table, which indicates the upper-level administrative area to which the administrative area belongs. The Code is as follows:Copy codeThe Code is as follows: select t. dm, t. mc, case
When substr (t. dm, 3) = '000000' then 1
When substr (t. dm, 5) = '00' then to_number (substr (t. dm, 0000) | '123 ')
When substr (t. dm, 5 )! = '00' then to_number (substr (t. dm, 1, 4) | '00 ')
Else 0
End sjbm from dm_xzqh t

The result is as follows:
No. DM MC SJBM
1 230000 Heilongjiang Province 1
2 230100 Harbin 230000
3 230101 city jurisdiction 230100
4 230102 daoli district 230100
5 232700 Daxinganling 230000
6 230103 Nangang district 230100
7 230104 outbound traffic 230100
8 230108 normal housing area 230100
9 230109 songbei district 230100
10 230110 xiangfang district 230100
11 230111 Hulan district 230100
12 230112 Acheng district 230100
13 230123 Yilan County 230100
14 238000 agricultural reclamation Bureau 230000
15 230124 founder County 230100
16 230125 binxian 230100
17 230126 Bayan County 230100
18 230127 Mulan county 230100
19 230128 Tonghe County 230100
20 230129 yanshou County 230100
21 230182 double city 230100
22 230183 Shangzhi city 230100
Then, you can use the oracle hierarchy to classify the queried data. The specific code is as follows:Copy codeThe Code is as follows: select level, dm, mc, sjbm from
(Select t. dm, t. mc, case
When substr (t. dm, 3) = '000000' then 1
When substr (t. dm, 5) = '00' then to_number (substr (t. dm, 0000) | '123 ')
When substr (t. dm, 5 )! = '00' then to_number (substr (t. dm, 1, 4) | '00') end sjbm from dm_xzqh t)
[Where level = 2] -- this condition statement is used to query the administrative divisions at each level.
Start with sjbm = 1
Connect by prior dm = sjbm
[Order by level];

The result is as follows:
No. level DM MC SJBM
1 230000 Heilongjiang Province 1 // province, level-> 1
2 2 230100 Harbin 230000 // city, level-> 2
3 3 230101 city jurisdiction 230100 // County, level-> 3
4 3 230102 daoli district 230100
5 3 230103 Nangang district 230100
6 3 230104 channel external zone 230100
7 3 230108 normal housing area 230100
8 3 230109 songbei 230100
9 3 230110 xiangfang district 230100
10 3 230111 Hulan district 230100
11 3 230112 Acheng district 230100
12 3 230123 Yilan County 230100
13 3 230124 founder County 230100
14 3 230125 binxian 230100
15 3 230126 Bayan County 230100
16 3 230127 Mulan county 230100
17 3 230128 Tonghe County 230100
18 3 230129 yanshou County 230100
19 3 230182 double city 230100
20 3 230183 Shangzhi city 230100
21 3 230184 Wuchang City 230100
22 2 232700 Daxinganling region 230000