偉大的祖國60華誕在即，為表達一名DBA的心聲，特完成了如下SQL實驗，明確一下祖國的行政區劃，抵制一切分裂祖國的行為:)！

1.建立測試表homeland_china
sec@ora10g> create table homeland_china (area varchar2(100), province varchar2(200));

2.初始化行政區劃資料
sec@ora10g> insert into homeland_china values
('華北' ,'北京市,天津市,河北省,山西省,內蒙古自治區');
sec@ora10g> insert into homeland_china values
('東北' ,'遼寧省,吉林省,黑龍江省');
sec@ora10g> insert into homeland_china values
('華東' ,'上海市,江蘇省,浙江省,安徽省,福建省,江西省,山東省');
sec@ora10g> insert into homeland_china values
('中南' ,'河南省,湖北省,湖南省,廣東省,廣西壯族自治區,海南省');
sec@ora10g> insert into homeland_china values
('西南' ,'重慶市,四川省,貴州省,雲南省,西藏自治區');
sec@ora10g> insert into homeland_china values
('西北' ,'陝西省,甘肅省,青海省,寧夏回族自治區,新疆維吾爾自治區');
sec@ora10g> insert into homeland_china values
('港澳臺','香港特別行政區,澳門特別行政區,臺灣省');

3.檢視初始化後的資料
sec@ora10g> col AREA for a10
sec@ora10g> col PROVINCE for a100
sec@ora10g> select * From homeland_china;

AREA       PROVINCE
---------- -----------------------------------------------------------
華北       北京市,天津市,河北省,山西省,內蒙古自治區
東北       遼寧省,吉林省,黑龍江省
華東       上海市,江蘇省,浙江省,安徽省,福建省,江西省,山東省
中南       河南省,湖北省,湖南省,廣東省,廣西壯族自治區,海南省
西南       重慶市,四川省,貴州省,雲南省,西藏自治區
西北       陝西省,甘肅省,青海省,寧夏回族自治區,新疆維吾爾自治區
港澳臺     香港特別行政區,澳門特別行政區,臺灣省

7 rows selected.

4.為祖國60華誕獻禮：上面初始化後的資料顯示效果是不是不太直觀？我們來使用一系列技巧來格式化上面的行政區劃資料，使之顯示更加清晰。
請細緻比較處理後的輸出和初始化資料之間的區別，一切自明。
sec@ora10g> break on area
sec@ora10g> col AREA for a10
sec@ora10g> select area,
2         province
3    from (select area,
4                 trim(substr(str, instr(str, ',', 1, level) + 1,
5                 instr(str, ',', 1, level + 1)
6                 - instr(str, ',', 1, level) - 1)) province,
7                 level lv,
8                 lag(level, 1, 0) over (partition by area order by level) lg
9            from (select area,
10                         ','||province||',' str
11                    from homeland_china
12                 )
13          connect by instr(str, ',', 1, level) > 0
14         )
15   where province is not null and lv != lg
16 /

AREA       PROVINCE
---------- -----------------------------------------------------------
東北        遼寧省
           吉林省
           黑龍江省
中南        河南省
           湖北省
           湖南省
           廣東省
           廣西壯族自治區
           海南省
華東        上海市
           江蘇省
           浙江省
           安徽省
           福建省
           江西省
           山東省
華北        北京市
           天津市
           河北省
           山西省
           內蒙古自治區
港澳臺      香港特別行政區
           澳門特別行政區
           臺灣省
西北      陝西省
           甘肅省
           青海省
           寧夏回族自治區
           新疆維吾爾自治區
西南        重慶市
           四川省
           貴州省
           雲南省
           西藏自治區

34 rows selected.

5.上面的SQL語句在10g中還可以進一步使用“正規表示式”技巧使之簡化，具體SQL如下
sec@ora10g> break on area
sec@ora10g> col AREA for a10
sec@ora10g> select area,
2         province
3    from (select area,
4                 regexp_substr(str, '[^,]+', 1, level) province,
5                 level lv,
6                 lag(level, 1, 0) over (partition by area order by level) lg
7            from (select area,
8                         ','||province str
9                    from homeland_china
10                 )
11          connect by regexp_substr(str, '[^,]+', 1, level) is not null
12         )
13 where lv != lg;

AREA       PROVINCE
---------- -----------------------------------------------------------
東北        遼寧省
           吉林省
           黑龍江省
中南        河南省
           湖北省
           湖南省
           廣東省
           廣西壯族自治區
           海南省
華東        上海市
           江蘇省
           浙江省
           安徽省
           福建省
           江西省
           山東省
華北        北京市
           天津市
           河北省
           山西省
           內蒙古自治區
港澳臺      香港特別行政區
           澳門特別行政區
           臺灣省
西北        陝西省
           甘肅省
           青海省
           寧夏回族自治區
           新疆維吾爾自治區
西南        重慶市
           四川省
           貴州省
           雲南省
           西藏自治區

34 rows selected.

6.小結
以上SQL語句請在SQL*Plus命令列中完成。

Best regards to our homeland China.

Good luck.

-- The End --

【實驗】【SQL】為祖國60華誕獻禮之格式化行政區劃

相關文章