...

11.10 Search for records containing the specified value on the reference chain and list their upper-level reference

 
spl-cookbook(224)

Perform self-join on a single table, get records containing the specified value, and list their superiors.
Find subordinate administrative regions of Hebei province based on China administrative division table.

ID NAME PARENT_ID
1 China 0
11 Beijing 1
12 Tianjin 1
13 Hebei 1
… … …
1301 Shijiazhuang 13
1302 Tangshan 13
… … …

SPL has P.nodes(F,r) function to get records having the specified value on the reference chain.

SPL script:

A
1 =connect(“db”)
2 =A1.query@x(“select * from ChinaRegion”)
3 >A2.switch(PARENT_ID,A2:ID)
4 =A2.select@1(name==“Hebei”)
5 =A2.nodes(PARENT_ID,A4)
6 =A5.new(ID,NAME,PARENT_ID.NAME:PARENT_NAME)

A1 Connect to the database.
A2 Query ChinaRegion table.
A3 Map foreign key PARENT_ID to the corresponding record to achieve self-join.
A4 Get the record of Hebei province.
A5 Use nodes() function to search references recursively to get records where the direct or indirect PARENT_ID is Hebei .
A6 Create a table sequence consisting of ID, region name and upper-level region name.

Execution result:

A5:

ID NAME PARENT_ID
1301 Shijiazhuang [13,Hebei,]
1302 Tangshan [13,Hebei,]
1303 Qinhuangdao [13,Hebei,]
1304 Handan [13,Hebei,]
1305 Xingtai [13,Hebei,]
… … …
130102 Chang’an District [1301,Shijiazhuang,]
130104 Qiaoxi District [1301,Shijiazhuang,]
130105 Xinhua District [1301,Shijiazhuang,]
130107 Jingxing mining area [1301,Shijiazhuang,]
130108 Yuhua District [1301,Shijiazhuang,]
130109 Gaocheng District [1301,Shijiazhuang,]
… … …

A6:

ID NAME PARENT_NAME
1301 Shijiazhuang Hebei
1302 Tangshan Hebei
1303 Qinhuangdao Hebei
1304 Handan Hebei
1305 Xingtai Hebei
… … …
130102 Changan District Shijiazhuang
130104 Qiaoxi District Shijiazhuang
130105 Xinhua District Shijiazhuang
130107 Jingxing mining area Shijiazhuang
130108 Yuhua District Shijiazhuang
130109 Gaocheng District Shijiazhuang
… … …
spl-cookbook(224)
 
 •  169 View  •