SQL 单字段变复合字段 行政区划树数据

行政区划全称计算与更新方案（以江苏省为例）

一、江苏省行政区划实例

1.1 江苏省行政区划代码示例

text

江苏省：320000000000
南京市：320100000000
    玄武区：320102000000
        新街口街道：320102001000
            长江路社区：320102001001
    秦淮区：320104000000
无锡市：320200000000
徐州市：320300000000
苏州市：320500000000

1.2 实际数据示例

AREA_CODE	AREA_NAME	AREA_LEVEL	期望的FULL_AREA_NAME
320000000000	江苏省	1	江苏省
320100000000	南京市	2	江苏省南京市
320102000000	玄武区	3	江苏省南京市玄武区
320102001000	新街口街道	4	江苏省南京市玄武区新街口街道
320102001001	长江路社区	5	江苏省南京市玄武区新街口街道长江路社区

二、技术实现方案

2.1 新增字段SQL

sql

-- 添加全行政区划名称字段
ALTER TABLE TBL_SYS_AREA_CODE ADD (FULL_AREA_NAME VARCHAR2(255));

-- 添加字段注释
COMMENT ON COLUMN TBL_SYS_AREA_CODE.FULL_AREA_NAME 
IS '全行政区划名称（如：江苏省南京市玄武区新街口街道长江路社区）';

2.2 针对江苏省的更新SQL（32开头）

sql

UPDATE TBL_SYS_AREA_CODE t 
SET t.FULL_AREA_NAME = (
    SELECT
        CASE
            -- 1级：省级（如：江苏省）
            WHEN A.AREA_LEVEL = '1' THEN 
                A.AREA_NAME
            
            -- 2级：市级（如：江苏省南京市）
            WHEN A.AREA_LEVEL = '2' THEN 
                (SELECT T.AREA_NAME 
                 FROM TBL_SYS_AREA_CODE T 
                 WHERE T.AREA_CODE = SUBSTR(A.AREA_CODE, 1, 2) || '0000000000')
                || A.AREA_NAME
            
            -- 3级：县级（如：江苏省南京市玄武区）
            WHEN A.AREA_LEVEL = '3' THEN 
                (SELECT T.AREA_NAME 
                 FROM TBL_SYS_AREA_CODE T 
                 WHERE T.AREA_CODE = SUBSTR(A.AREA_CODE, 1, 2) || '0000000000')
                || (SELECT T.AREA_NAME 
                    FROM TBL_SYS_AREA_CODE T 
                    WHERE T.AREA_CODE = SUBSTR(A.AREA_CODE, 1, 4) || '00000000')
                || A.AREA_NAME
            
            -- 4级：乡镇级（如：江苏省南京市玄武区新街口街道）
            WHEN A.AREA_LEVEL = '4' THEN 
                (SELECT T.AREA_NAME 
                 FROM TBL_SYS_AREA_CODE T 
                 WHERE T.AREA_CODE = SUBSTR(A.AREA_CODE, 1, 2) || '0000000000')
                || (SELECT T.AREA_NAME 
                    FROM TBL_SYS_AREA_CODE T 
                    WHERE T.AREA_CODE = SUBSTR(A.AREA_CODE, 1, 4) || '00000000')
                || (SELECT T.AREA_NAME 
                    FROM TBL_SYS_AREA_CODE T 
                    WHERE T.AREA_CODE = SUBSTR(A.AREA_CODE, 1, 6) || '000000')
                || A.AREA_NAME
            
            -- 5级：村级（如：江苏省南京市玄武区新街口街道长江路社区）
            WHEN A.AREA_LEVEL = '5' THEN 
                (SELECT T.AREA_NAME 
                 FROM TBL_SYS_AREA_CODE T 
                 WHERE T.AREA_CODE = SUBSTR(A.AREA_CODE, 1, 2) || '0000000000')
                || (SELECT T.AREA_NAME 
                    FROM TBL_SYS_AREA_CODE T 
                    WHERE T.AREA_CODE = SUBSTR(A.AREA_CODE, 1, 4) || '00000000')
                || (SELECT T.AREA_NAME 
                    FROM TBL_SYS_AREA_CODE T 
                    WHERE T.AREA_CODE = SUBSTR(A.AREA_CODE, 1, 6) || '000000')
                || (SELECT T.AREA_NAME 
                    FROM TBL_SYS_AREA_CODE T 
                    WHERE T.AREA_CODE = SUBSTR(A.AREA_CODE, 1, 9) || '000')
                || A.AREA_NAME
            
        END AS FULL_NAME
    FROM TBL_SYS_AREA_CODE A
    WHERE A.AREA_CODE = t.AREA_CODE
)
WHERE t.AREA_CODE LIKE '32%';  -- 只更新江苏省的数据（32开头）

三、分步执行说明

3.1 第一步：添加字段

sql

-- 检查表结构
DESC TBL_SYS_AREA_CODE;

-- 添加新字段
ALTER TABLE TBL_SYS_AREA_CODE ADD (FULL_AREA_NAME VARCHAR2(255));

-- 验证字段添加
SELECT column_name, data_type, data_length 
FROM user_tab_columns 
WHERE table_name = 'TBL_SYS_AREA_CODE' 
AND column_name = 'FULL_AREA_NAME';

3.2 第二步：测试单个行政区划

sql

-- 测试江苏省
SELECT 
    AREA_CODE,
    AREA_NAME,
    AREA_LEVEL,
    CASE
        WHEN AREA_LEVEL = '1' THEN AREA_NAME
        WHEN AREA_LEVEL = '2' THEN 
            (SELECT AREA_NAME FROM TBL_SYS_AREA_CODE 
             WHERE AREA_CODE = '320000000000') || AREA_NAME
        WHEN AREA_LEVEL = '3' THEN 
            (SELECT AREA_NAME FROM TBL_SYS_AREA_CODE 
             WHERE AREA_CODE = '320000000000') ||
            (SELECT AREA_NAME FROM TBL_SYS_AREA_CODE 
             WHERE AREA_CODE = SUBSTR('320102000000', 1, 4) || '00000000') ||
            AREA_NAME
    END AS TEST_FULL_NAME
FROM TBL_SYS_AREA_CODE 
WHERE AREA_CODE IN ('320000000000', '320100000000', '320102000000');

3.3 第三步：执行批量更新

sql

-- 先备份（建议）
CREATE TABLE TBL_SYS_AREA_CODE_BACKUP_2024 AS 
SELECT * FROM TBL_SYS_AREA_CODE WHERE AREA_CODE LIKE '32%';

-- 执行更新
UPDATE TBL_SYS_AREA_CODE t 
SET t.FULL_AREA_NAME = (...完整SQL...) 
WHERE t.AREA_CODE LIKE '32%';

-- 提交事务
COMMIT;

3.4 第四步：验证结果

sql

-- 查看更新结果
SELECT 
    AREA_CODE,
    AREA_NAME,
    AREA_LEVEL,
    FULL_AREA_NAME,
    LENGTH(FULL_AREA_NAME) AS NAME_LENGTH
FROM TBL_SYS_AREA_CODE 
WHERE AREA_CODE LIKE '32%'
ORDER BY AREA_CODE;

-- 检查是否有空值
SELECT COUNT(*) 
FROM TBL_SYS_AREA_CODE 
WHERE AREA_CODE LIKE '32%' 
AND FULL_AREA_NAME IS NULL;

-- 检查各级别的全称
SELECT 
    AREA_LEVEL,
    COUNT(*) AS COUNT,
    MIN(FULL_AREA_NAME) AS EXAMPLE
FROM TBL_SYS_AREA_CODE 
WHERE AREA_CODE LIKE '32%'
GROUP BY AREA_LEVEL
ORDER BY AREA_LEVEL;

四、优化版本（递归CTE实现）

4.1 使用递归CTE查询层级

sql

WITH AREA_HIERARCHY AS (
    -- 基础层级
    SELECT 
        AREA_CODE,
        AREA_NAME,
        AREA_LEVEL,
        AREA_NAME AS FULL_NAME,
        1 AS LEVEL_NUM
    FROM TBL_SYS_AREA_CODE 
    WHERE AREA_LEVEL = '1'
    AND AREA_CODE LIKE '32%'
    
    UNION ALL
    
    -- 递归连接
    SELECT 
        c.AREA_CODE,
        c.AREA_NAME,
        c.AREA_LEVEL,
        p.FULL_NAME || c.AREA_NAME AS FULL_NAME,
        p.LEVEL_NUM + 1
    FROM TBL_SYS_AREA_CODE c
    INNER JOIN AREA_HIERARCHY p 
        ON c.PARENT_AREA_CODE = p.AREA_CODE
    WHERE c.AREA_CODE LIKE '32%'
)
SELECT * FROM AREA_HIERARCHY 
ORDER BY AREA_CODE;

4.2 基于递归CTE的更新

sql

MERGE INTO TBL_SYS_AREA_CODE t
USING (
    WITH AREA_HIERARCHY AS (
        SELECT 
            AREA_CODE,
            AREA_NAME,
            AREA_LEVEL,
            AREA_NAME AS FULL_NAME,
            1 AS LEVEL_NUM
        FROM TBL_SYS_AREA_CODE 
        WHERE AREA_LEVEL = '1'
        AND AREA_CODE LIKE '32%'
        
        UNION ALL
        
        SELECT 
            c.AREA_CODE,
            c.AREA_NAME,
            c.AREA_LEVEL,
            p.FULL_NAME || c.AREA_NAME AS FULL_NAME,
            p.LEVEL_NUM + 1
        FROM TBL_SYS_AREA_CODE c
        INNER JOIN AREA_HIERARCHY p 
            ON c.PARENT_AREA_CODE = p.AREA_CODE
        WHERE c.AREA_CODE LIKE '32%'
    )
    SELECT AREA_CODE, FULL_NAME
    FROM AREA_HIERARCHY
) s
ON (t.AREA_CODE = s.AREA_CODE)
WHEN MATCHED THEN 
    UPDATE SET t.FULL_AREA_NAME = s.FULL_NAME;

五、Java代码实现（备选方案）

java

@Service
public class AreaCodeService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    /**
     * 计算行政区划全称
     */
    public String calculateFullAreaName(String areaCode) {
        String sql = """
            WITH PARENT_NAMES AS (
                -- 省级
                SELECT AREA_NAME FROM TBL_SYS_AREA_CODE 
                WHERE AREA_CODE = SUBSTR(?, 1, 2) || '0000000000'
                UNION ALL
                -- 市级（如果有）
                SELECT AREA_NAME FROM TBL_SYS_AREA_CODE 
                WHERE AREA_CODE = SUBSTR(?, 1, 4) || '00000000'
                AND LENGTH(?) >= 4
                UNION ALL
                -- 县级（如果有）
                SELECT AREA_NAME FROM TBL_SYS_AREA_CODE 
                WHERE AREA_CODE = SUBSTR(?, 1, 6) || '000000'
                AND LENGTH(?) >= 6
                UNION ALL
                -- 乡镇级（如果有）
                SELECT AREA_NAME FROM TBL_SYS_AREA_CODE 
                WHERE AREA_CODE = SUBSTR(?, 1, 9) || '000'
                AND LENGTH(?) >= 9
                UNION ALL
                -- 当前级
                SELECT AREA_NAME FROM TBL_SYS_AREA_CODE 
                WHERE AREA_CODE = ?
            )
            SELECT LISTAGG(AREA_NAME, '') WITHIN GROUP (ORDER BY ROWNUM) 
            FROM PARENT_NAMES
            WHERE AREA_NAME IS NOT NULL
            """;
        
        return jdbcTemplate.queryForObject(sql, String.class, 
            areaCode, areaCode, areaCode, areaCode, areaCode, areaCode, areaCode, areaCode);
    }
    
    /**
     * 批量更新行政区划全称
     */
    @Transactional
    public void batchUpdateFullNames(String provincePrefix) {
        String updateSql = """
            UPDATE TBL_SYS_AREA_CODE t 
            SET FULL_AREA_NAME = (
                SELECT (
                    CASE AREA_LEVEL
                        WHEN '1' THEN AREA_NAME
                        WHEN '2' THEN 
                            (SELECT AREA_NAME FROM TBL_SYS_AREA_CODE 
                             WHERE AREA_CODE = SUBSTR(t.AREA_CODE, 1, 2) || '0000000000')
                            || AREA_NAME
                        -- ... 其他级别类似
                    END
                )
                FROM TBL_SYS_AREA_CODE a 
                WHERE a.AREA_CODE = t.AREA_CODE
            )
            WHERE AREA_CODE LIKE ? || '%'
            """;
        
        jdbcTemplate.update(updateSql, provincePrefix);
    }
}

六、使用示例和验证

6.1 查询验证

sql

-- 查看南京市的全称
SELECT * FROM TBL_SYS_AREA_CODE 
WHERE AREA_CODE = '320100000000';

-- 查看玄武区的全称  
SELECT * FROM TBL_SYS_AREA_CODE 
WHERE AREA_CODE = '320102000000';

-- 查看新街口街道的全称
SELECT * FROM TBL_SYS_AREA_CODE 
WHERE AREA_CODE = '320102001000';

-- 查看长江路社区的全称
SELECT * FROM TBL_SYS_AREA_CODE 
WHERE AREA_CODE = '320102001001';

6.2 性能测试

sql

-- 检查执行计划
EXPLAIN PLAN FOR
UPDATE TBL_SYS_AREA_CODE t 
SET t.FULL_AREA_NAME = (...完整SQL...)
WHERE t.AREA_CODE LIKE '32%';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 统计更新数量
SELECT COUNT(*) FROM TBL_SYS_AREA_CODE 
WHERE AREA_CODE LIKE '32%';

-- 检查索引使用
CREATE INDEX IDX_AREA_CODE ON TBL_SYS_AREA_CODE(AREA_CODE);
CREATE INDEX IDX_AREA_LEVEL ON TBL_SYS_AREA_CODE(AREA_LEVEL);

七、注意事项

1. 数据完整性：确保所有行政区划代码都符合国家标准
2. 性能考虑：江苏省有13个地级市、96个县区、上千个乡镇，更新时考虑分批进行
3. 事务管理：建议在业务低峰期执行，并做好数据备份
4. 编码规范：行政区划名称中不要包含空格或其他特殊字符
5. 长度限制：VARCHAR2(255)最多可容纳约85个汉字，足够存储完整路径

八、扩展建议

1. 增量更新：新建触发器，当插入新行政区划时自动计算全称
2. 缓存机制：在应用层缓存常用行政区划的全称
3. 查询优化：为FULL_AREA_NAME字段建立索引，支持模糊查询
4. 数据校验：定期检查FULL_AREA_NAME字段的完整性和一致性

这个方案可以确保江苏省所有行政区划都能正确生成完整的全称路径，便于后续的数据展示和查询。