产业结构层次化转换

• 实现目标

  将表ODBC_IMPORT_TEST数据格式如图所示机构，其中INDEX_CODE代表产业编号，FATHER_CODE代表父级产业编号
  
  转化为如图所示表INDUSTRY_CENGCI结构
  

• 逻辑思路

  查询一级产业对应的目表格式
  
  查询二级产业对应的目表格式
  
  查询三级产业对应的目表格式
  

• 实现步骤

  创建目标表结构

CREATE TABLE INDUSTRY_CENGCI(
"一级产业编号" VARCHAR2(50),
"一级产业名称" VARCHAR2(50),
"二级产业编号" VARCHAR2(50),
"二级产业名称" VARCHAR2(50),
"三级产业编号" VARCHAR2(50),
"三级产业名称" VARCHAR2(50));

查询一级产业对应的目表格式

SELECT INDEX_CODE AS "一级产业编号",INDEX_NAME AS "一级产业名称",NULL AS "二级产业编号"
,NULL AS "二级产业名称",NULL AS "三级产业编号",NULL AS "三级产业名称"
FROM ODBC_IMPORT_TEST

创建查询二级产业对应的目标格式的视图

CREATE OR REPLACE VIEW SECOND_INDUSTRY
AS
SELECT A.FATHER_CODE AS "一级产业编号",B.INDEX_NAME AS "一级产业名称",
A.INDEX_CODE AS "二级产业编号",A.INDEX_NAME AS "二级产业名称",
NULL AS "三级产业编号",NULL AS "三级产业名称"
FROM 
--利用LEVEL层次化查询，将二层产业结构的数据从表ODBC_IMPORT_TEST查询出来
--然后与表关联的到一级产业名称
(
SELECT LEVEL,INDEX_CODE,FATHER_CODE ,INDEX_NAME
FROM ODBC_IMPORT_TEST
WHERE LEVEL = 2
START WITH FATHER_CODE IS NULL
CONNECT BY FATHER_CODE = PRIOR INDEX_CODE
) A,ODBC_IMPORT_TEST B
WHERE A.FATHER_CODE = B.INDEX_CODE;

查询三级产业对应的目标格式

SELECT  "一级产业编号", "一级产业名称",
"二级产业编号", "二级产业名称", 
C.INDEX_CODE AS"三级产业编号",C.INDEX_NAME AS "三级产业名称"
--由于上面创建了二级产业视图，直接查询二级产业视图与表SECOND_INDUSTRY得到三级产
--业编号和名称
FROM ODBC_IMPORT_TEST C,SECOND_INDUSTRY D
WHERE D."二级产业编号" = C.FATHER_CODE);

• 将数据插入目标表中

INSERT INTO INDUSTRY_CENGCI
(
SELECT INDEX_CODE AS "一级产业编号",INDEX_NAME AS "一级产业名称",NULL AS "二级产业编号",NULL AS "二级产业名称",NULL AS "三级产业编号",NULL AS "三级产业名称"
FROM ODBC_IMPORT_TEST
WHERE FATHER_CODE IS NULL
UNION
SELECT * FROM SECOND_INDUSTRY
UNION 
SELECT  "一级产业编号", "一级产业名称",
"二级产业编号", "二级产业名称", 
C.INDEX_CODE AS"三级产业编号",C.INDEX_NAME AS "三级产业名称"
FROM ODBC_IMPORT_TEST C,SECOND_INDUSTRY D
WHERE D."二级产业编号" = C.FATHER_CODE);