树形结构表中，查询当前级别以及其所有字节点的思路

背景

项目中使用到一个行政区划表：area_code；要求查询某个行政区划以及其下属的所有行政区划信息。

表结构

数据库

• postgreSQL

说明

行政区划是有规则的，如：

城市	行政区划代码
北京	11 00 00 000 000
市辖区	11 01 00 000 000
东城区	11 01 01 00 00 00
东华门街道	11 01 01 001 000
多福巷社区居委会	11 01 01 001 001
银闸社区居委会	11 01 01 001 002

不难发现，行政区划代码规则就是：2位省级、2位市级、2位区/县级、3位乡/镇级、3位村/社区级。于是乎，我想是否能够通过截取调后面的0，然后进行like？
比如，我想查询北京市东城区及下属辖区的行政区划信息：

方式一（截取后年多余的0）

需要截取了8位“0”的位数：

• 村/社区：3位
• 乡/镇：3位
• 区/县：2位

select * from area_code where code like '1101%';

以此类推！理论上这个方法是没有问题的，就是截取后面的“0”的时候，必须要按规则进行截取相应位数的“0”。

方式二（新加一个维护字段-isn）

数据存储为：

城市	行政区划代码	isn
北京	110000000000	110000000000
市辖区	110100000000	110000000000.110100000000
东城区	110101000000	110000000000.110100000000.110101000000
东华门街道	110101001000	110000000000.110100000000.110101000000.110101001000
多福巷社区居委会	110101001001	110000000000.110100000000.110101000000.110101001000.110101001001
银闸社区居委会	110101001002	110000000000.110100000000.110101000000.110101001000.110101001002

此处查询只需要按isn字段右like即可；

select * from area_code where isn like '110000000000.110100000000.110101000000%';

新问题

添加新字段固然方便，但是需要对字段进行维护；如果是项目初期的时候，可以直接设计这个字段。但是，要是再后期添加，就需要对字段进行维护；
这里记录一下，维护代码。

postgreSQL:

with recursive t as (
    select code,name,pcode,level,code as path
    from area_code
    where pcode = '0'
    union
        select d.code,d.name,d.pcode,d.level,(t.path || '.' || d.code) as path
    from area_code d join t on d.pcode = t.code
)
select t.* from t

这样就可以查询出所有行政区划的isn了；

这里再贴上MySQL的代码。

MySQL：

select t.code,t.name,t.pcode,t.path as isn
from (
SELECT *,
@Pn:=code
,@path:=(
    SELECT GROUP_CONCAT(
        SUBSTRING_INDEX(@Pn:= (
            SELECT CONCAT(pcode,'|',code)
            FROM area_code
            WHERE code = SUBSTRING_INDEX(@Pn, '|', 1)
        ),
        '|',
        -1) ORDER BY code DESC SEPARATOR '.')
    FROM area_code WHERE @Pn IS NOT NULL ORDER BY code ASC
) AS path
,LENGTH(@path) - LENGTH(REPLACE(@path, '.', '')) + 1 AS level
FROM area_code
) t order by t.code;

查询出来以后，可以导出或者利用DBMS管理工具来ctrl+C、ctrl+V一下，写入到isn字段中。
另外，在程序保存或者修改数据的时候（一般都是基础数据，无需修改的），需要对isn进行维护。